﻿using System;
using System.IO;
using System.Text;
using Microsoft.Office.Interop.Excel;
using TpAgentCore.Properties;
using TpBaseObjects.Base;
using TpTools;
using TpExtensions;
using System.Reflection;
using System.Globalization;
using TpData.Manager;
using TpContracts;
using TpBaseObjects.Objects;
using System.Collections.Generic;

namespace TpAgentCore.Base {

    public class Agent : AbstractExcelTools {

        #region Members


        private delegate int delegateFreeRowNumber(string bereich, Worksheet sheet);
        public enum CallNumber {
            All = 0,
            Aepü = 1,
            UpdateJobevidenz,
            UpdateLeistungen,
            UpdateJobEvidenzInternBulk,
            UpdateLeistungenInternBulk,
            Monatsabschluss,
            KorrekturNnle,
            UpdateJobKataloge,
            CheckLeistungen,
            ExportAccountData,
            UpdateJobsFromTecOffice
        }

        private const string logArea = "TpAgentCore.Base.Agent";
        private const string errType = "Err";
        private const string logType = "Log";
        private const int daysForWorkInPreviousMonth = 10;

        #endregion

        #region Constructor

        public Agent() {
            Settings.Default.Reset();
            Settings.Default.Reload();
        }

        #endregion

        #region Overrides

        public override void SetTpUser() {
            throw new NotImplementedException();
        }

        #endregion

        #region Methods

        private void AddHyperlink(Range cellRange, string hyperLink) {
            if (hyperLink != "") {
                object textToDisplay;
                if (cellRange.Value != null) {
                    textToDisplay = cellRange.Value;
                } else {
                    textToDisplay = "HyperLinkTo";
                }
                cellRange.Hyperlinks.Add(Anchor: cellRange, Address: hyperLink, TextToDisplay: textToDisplay);
            }
        }

        public void ChangeView(Worksheet sh, DateTime from, DateTime to) {
            FinalJobOrderRows(sh);
            FinalJobSetFontAndFormulas(sh, from, to);
        }

        public void ChangeView(Worksheet sh, DateTime from, DateTime to, Extensions.tpContent contentType, Extensions.tpSorting sortingType, Extensions.tpSortOrder sortOrder, Extensions.tpShow showType) {
            if (showType != Extensions.tpShow.List) {
                FinalJobOrderRows(sh, sortingType, sortOrder);
                FinalJobSetFontAndFormulas(sh, from, to, contentType);
            }
        }

        public void CheckLeistungsZeilen(string path) {
            CheckLeistungsZeilen(path, 0);
        }

        public void CheckLeistungsZeilen(string path, int monthMM) {
            Workbook wb = null;
            Worksheet sh = null;
            string sheetName = "";

            try {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start CheckLeistungsZeilen", false);
                if (path == null) {
                    path = MaPath;
                }
                string[] myFiles = Directory.GetFiles(path, Settings.Default.FilesNnle);
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Anzahl Files: " + myFiles.Length.ToString(), false);

                foreach (string file in myFiles) {
                    try {
                        if (WorkBookIsLoaded(Path.GetFileName(file))) {
                            wb = this.GetWorkbook(Path.GetFileName(file));
                        } else {
                            if (File.Exists(file) & !Tools.FileIsInUse(file)) {
                                wb = exApp.Workbooks.Open(file);
                            }
                        }

                        if (wb == null) {
                            if (!File.Exists(file) || Tools.FileIsInUse(file)) {
                                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CheckLeistungsZeilen: " + String.Format(Resources.FileIsInUseOrNotExists, file), true);
                                continue;
                            }
                        }

                        sheetName = DateTime.Now.AddDays(-1).Month.ToString("00");
                        if (monthMM > 0) {
                            sheetName = monthMM.ToString("00");
                        }

                        if (sheetName == null | sheetName.Length == 0) {
                            LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CheckLeistungsZeilen: sheetName is not set! (" + file + ")", true);
                            continue;
                        }

                        sh = wb.Worksheets[sheetName];
                        if (sh != null) {
                            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "CheckLeistungsZeilen: (" + wb.Name + "/" + sh.Name + ")", false);
                            if (CheckLeistungsRow(sh)) {
                            } else {
                                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CheckLeistungsZeilen: " + wb.Name + "/" + sh.Name + ") CheckLeistungsRow = false", true);
                            }
                        } else {
                            LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CheckLeistungsZeilen: (" + DateTime.Now.AddDays(-1).Month.ToString("00") + ") not found!", true);
                        }
                    } catch (Exception ex) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CheckLeistungsZeilen: " + ex.Message, true);
                    } finally {
                        if (wb != null) {
                            wb.Save();
                            wb.Close(SaveChanges: true);
                            wb = null;
                        }
                    }
                }

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CheckLeistungsZeilen: (" + wb.Name + "): " + ex.Message, true);
            } finally {
                CloseAllWorkbooks(true);
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Ende CheckLeistungsZeilen", false);
            }
        }

        public void ClearJobSheet(Worksheet sh, Microsoft.Office.Interop.Excel.Application exApp, out int endRow) {
            int startRow = 3;
            Range r1 = sh.Rows[startRow];
            r1.EntireRow.Hidden = false;
            ProjectRow row = new ProjectRow(r1);
            endRow = 0;
            while (startRow < 1000) {
                if (row.IsGesamtSummenZeile) {
                    endRow = startRow;
                    return;
                }
                if (row.IsEmpty) {
                    Range del = sh.Rows[startRow, Missing.Value];
                    //del.Select();
                    del.Delete(Shift: XlDeleteShiftDirection.xlShiftUp);
                } else {
                    startRow += 1;
                }
                r1 = sh.Rows[startRow];
                r1.EntireRow.Hidden = false;
                row = new ProjectRow(r1);
            }
        }

        private void ClearRow(AeRow source) {

            #region Projekt

            source.ProjektName = "";
            source.ProjektBezeichnung = "";

            #endregion

            #region Adresse

            source.Plz = "";
            source.Ort = "";
            source.Straße = "";

            #endregion

            #region Erlös

            source.Erlösminderung = 0;
            source.Forderungsausfall = 0;
            source.RabattGesamt = 0;
            source.RabattLeistungenGesamt = 0;
            source.RabattProdukteGesamt = 0;
            source.Schadenfall = 0;
            source.SummeHandelsware = 0;
            source.SummeMontage = 0;
            source.SummeProduktion = 0;

            #endregion

            #region Kosten

            source.KostenAdministration = 0;
            source.KostenHandelsware = 0;
            source.KostenMontage = 0;
            source.KostenProduktion = 0;

            #endregion

            #region Zusatzkosten

            source.ZkBaustellenInfrastrukturFix = 0;
            source.ZkBaustellenInfrastrukturProzent = 0;
            source.ZkErfüllungsgarantie = 0;
            source.ZkHaftrücklass = 0;
            source.ZkProvisionDB = 0;
            source.ZkProvisionErlösFix = 0;
            source.ZkProvisionErlösProzent = 0;
            source.ZkSkonto = 0;

            #endregion

            #region Zeitaufwand

            source.ZeitaufwandAdministration = 0;
            source.ZeitaufwandMontage = 0;
            source.ZeitaufwandProduktion = 0;

            #endregion


        }

        private void CopyInternDetail(Worksheet sourceSheet, Worksheet targetSheet, ProjectRow row, int freeRowNumber) {
            CopyInternDetail(sourceSheet, targetSheet, row, freeRowNumber, false);
        }

        private void CopyInternDetail(Worksheet sourceSheet, Worksheet targetSheet, ProjectRow row, int freeRowNumber, bool clearCells) {
            Range targetRow = targetSheet.Rows[freeRowNumber];
            Range sourceRow = row.MyRow;
            SetFormula(row.RowNumber, sourceSheet, row, false);
            ((Microsoft.Office.Interop.Excel._Worksheet)sourceSheet).Activate();
            sourceRow.Select();
            sourceRow.Copy();
            ((Microsoft.Office.Interop.Excel._Worksheet)targetSheet).Activate();
            targetRow.Select();
            targetRow.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
            targetRow.PasteSpecial(XlPasteType.xlPasteComments, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
            ProjectRow tpRow = new ProjectRow(targetRow);
            if (clearCells) {
                string errMsg = "";
                tpRow.ClearTpCells(out errMsg);
                if (errMsg != "") {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyInternDetail: tpRow.ClreaTpCells (" + errMsg + ")", true);
                }
            }
            //targetRow.Font.Color = sourceRow.Font.Color;

        }

        private void ClearInternRow(bool clearContent, ProjectRow row, string fileSuffix) {
            if (clearContent) {
                Range sourceRow = row.MyRow;
                //sourceRow.Delete(Shift:XlDeleteShiftDirection.xlShiftUp);
                sourceRow.ClearContents();
                sourceRow.ClearComments();
                //sourceRow.Font.Name = Extensions.tpFontNormal;
                Extensions.SetFontBoldOrNormal(sourceRow, Extensions.tpFont.Regular);
            } else {

                row.SetExported(fileSuffix.Substring(0, 1).ToUpper() + fileSuffix.Substring(1).ToLower());
            }
        }

        private bool CopyLeistungenInternRow(Worksheet sourceSheet, ProjectRow row, string userName, string fileSuffix, bool useJobJahr, string sheetNameSuffix, bool clearContent, delegateFreeRowNumber myFreeRowNumber) {
            return CopyLeistungenInternRow(sourceSheet, row, userName, fileSuffix, useJobJahr, sheetNameSuffix, clearContent, myFreeRowNumber, false);
        }

        private bool CopyLeistungenInternRow(Worksheet sourceSheet, ProjectRow row, string userName, string fileSuffix, bool useJobJahr, string sheetNameSuffix, bool clearContent, delegateFreeRowNumber myFreeRowNumber, bool clearCells) {

            string targetBookName = string.Empty;
            string targetFileName = string.Empty;

            try {

                string myPath = MaPath;
                string exusPath = ExusPath;

                if (row.JobDatum.Month != DateTime.Today.AddDays(-1).Month) {
                    myPath = myPath.Replace(DateTime.Today.AddDays(-1).Year.ToString().Right(2), row.JobDatum.Year.ToString().Right(2));
                }
                targetBookName = userName + fileSuffix + (useJobJahr ? row.JobDatum.Year.ToString().Right(2) : "") + Settings.Default.LeExportFileExtension;
                targetFileName = Path.Combine(myPath, targetBookName);

                ProjectSheet source = new ProjectSheet(sourceSheet, true);
                Workbook targetBook = null;
                if (WorkBookIsLoaded(Path.GetFileName(targetBookName))) {
                    targetBook = this.GetWorkbook(Path.GetFileName(targetBookName));
                } else {
                    if (File.Exists(targetFileName) & !Tools.FileIsInUse(targetFileName)) {
                        targetBook = exApp.Workbooks.Open(targetFileName);
                    } else {
                        targetFileName = Path.Combine(exusPath, targetBookName);
                        if (File.Exists(targetFileName) & !Tools.FileIsInUse(targetFileName)) {
                            targetBook = exApp.Workbooks.Open(targetFileName);
                        } else {
                            LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, String.Format(Resources.FileIsInUseOrNotExists, targetFileName), true);
                            return false;
                        }
                    }
                }
                if (targetBook != null) {
                    Worksheet targetSheet = null;
                    string targetSheetName = row.JobDatum.Month.ToString("00") + sheetNameSuffix;

                    if (DateTime.Today.Day > daysForWorkInPreviousMonth) {
                        if ((row.JobDatum.Month != DateTime.Today.Month
                            && row.JobDatum.Year == DateTime.Today.Year)
                            || row.JobDatum.Year != DateTime.Today.Year) {
                            targetSheetName = DateTime.Today.Month.ToString("00") + sheetNameSuffix;
                        }
                    }

                    targetSheetName = GetTargetSheetName(targetSheetName, targetBook.Name, fileSuffix);
                    targetSheet = targetBook.Sheets[targetSheetName];

                    if (targetSheet != null) {
                        if (!row.IsReadyToStorno) {
                            int freeRowNumber = myFreeRowNumber(fileSuffix, targetSheet);
                            if (freeRowNumber != 0) {
                                CopyInternDetail(sourceSheet, targetSheet, row, freeRowNumber, clearCells);
                                SetInternRow(fileSuffix, row, userName);
                                ClearInternRow(clearContent, row, fileSuffix);
                            } else {
                                row.MarkAsFailed();
                                return false;
                            }
                        } else {
                            DeleteInternRow(targetSheet, row);
                        }

                        SortInternFile(targetSheet, fileSuffix);

                    }
                } else {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyLeistungenInternRow: targetBook (" + targetBookName + ") not found", true);
                }
                if (targetBook != null) {
                    targetBook.Save();
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyLeistungenInternRow: " + ex.Message, true);
                row.MarkAsFailed();
                return false;
            }
            return true;
        }

        private void CopyJob2LeFile(JobObj job) {
            string user = TecUserDataManager.LoadTecUser(job.UserID).LoginName.Trim();
            string targetBookName = user + "le" + job.Datum.Year.ToString().Right(2)  + Settings.Default.LeExportFileExtension;
            string targetFileName = Path.Combine(MaPath, targetBookName);
            Workbook wb = null;
            Worksheet sh = null;

            #region Check File exists or is in use

            if (!File.Exists(targetFileName)) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyJob2LeFile: " + String.Format(Resources.FileIsInUseOrNotExists, targetFileName), true);
                return;
            }

            if (Tools.FileIsInUse(targetFileName)) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyJob2LeFile: " + String.Format(Resources.FileIsInUseOrNotExists, targetFileName), true);
                return;
            }

            #endregion

            try {
                if (exApp == null) {
                    exApp = Office.GetExcelApplication();
                }
                if (exApp == null) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyJob2LeFile: exApp is null", true);
                    return;
                }

                wb = exApp.Workbooks.Open(targetFileName);
                sh = wb.Sheets[job.Datum.Month.ToString("00")];

                int freeRowNumber = GetLeFreeRowNumber("le", sh);

                Range row = sh.Rows[freeRowNumber, Missing.Value];

                ProjectRow jobRow = new ProjectRow(row);
                
                jobRow.ClearContent();

                jobRow.Durchführender1 = user;
                jobRow.JobDatum = job.Datum;
                jobRow.Fahrzeug = job.Fahrzeug;
                jobRow.Leistung = job.Leistung;
                jobRow.LeistungProzent = job.LeistungProzent;
                jobRow.LeistungZeit = job.LeistungZeit;
                jobRow.LeistungZeitSumme = job.LeistungZeit;
                jobRow.Bereich = job.Area;
                jobRow.Teilbereich = job.SubArea;
                jobRow.Subbereich = job.Sb;
                jobRow.SubSubBereich = job.Ss;
                jobRow.Datei = job.File;
                jobRow.Firma = job.Fa;
                jobRow.Vr = job.Vr;
                if (job.Einheit == "Pa") {
                    jobRow.PauschalZeit = job.LeistungZeit;
                }
                jobRow.JobCharacter = "D";
                jobRow.JobTime = (job.StartTime.Hours + job.StartTime.Minutes / 100.0 + job.StartTime.Seconds / 10000.0) * (job.StartTime > TimeSpan.Zero ? 1 : -1);
                jobRow.JobEndTime = (job.EndTime.Hours + job.EndTime.Minutes / 100.0 + job.EndTime.Seconds / 10000.0) * (job.EndTime > TimeSpan.Zero ? 1 : -1);
                
                job.Status = (short)Extensions.JobStatus.Exportiert;
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "CopyJob2LeFile: " + job.JobID.ToString() + ", " + job.Area + ", " + job.Leistung, false);

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyJob2LeFile: " + ex.Message, true);
            } finally {
                if (wb != null) {
                    wb.Save();
                    wb.Close(SaveChanges: true);
                }
            }
        }

        private void CopyJobevidenzRow(Worksheet sourceSheet, Worksheet targetSheet, Range sourceRow) {
            Range r1 = sourceRow.Cells[1, "H"];
            Range r2 = sourceRow.Cells[1, "BB"];
            Range r3 = sourceRow.Cells[1, "BA"];
            Range r4 = sourceRow.Cells[1, "AH"];

            string v2 = "";
            string v3 = "";
            if (r2.Value != null) {
                v2 = r2.Value.ToString();
            }
            if (r3.Value != null) {
                v3 = r3.Value.ToString();
            }
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "CopyJobevidenzRow: " + v2 + " (" + (r4.Value == null ? "" : r4.Value.ToString() + " / ") + r3.Value.ToString() + ")", false);
            try {
                string jeArea = Tools.Nz(Convert.ToString(r1.Value), "");
                string subArea = "";
                if (r3.Value != null) {
                    subArea = Convert.ToString(r3.Value);
                }

                string hyperLink = Tools.Nz(Convert.ToString(r2.Value), "");

                //int freeRowNumber = GetInFreeRowNumber(targetSheet, jeArea, subArea);
                int freeRowNumber = GetJeFreeRowNumber(jeArea, targetSheet);
                if (freeRowNumber == 0) {
                    ProjectRow sourceProjectRow = new ProjectRow(sourceRow);
                    sourceProjectRow.MarkAsFailed();
                    return;
                }

                Range targetRow = GetNewRow(targetSheet, freeRowNumber);
                ((Microsoft.Office.Interop.Excel._Worksheet)sourceSheet).Activate();
                sourceRow.Select();
                sourceRow.Copy();
                ((Microsoft.Office.Interop.Excel._Worksheet)targetSheet).Activate();
                targetRow.Select();
                targetRow.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                targetRow.PasteSpecial(XlPasteType.xlPasteComments, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                targetRow.Font.Color = sourceRow.Font.Color;

                SetRowSettings(targetRow, hyperLink);
                ProjectRow newRow = new ProjectRow(targetRow);
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Update Jobevidenz: " + newRow.KundenName + newRow.Spalte("D") + ", " + targetSheet.Name + ";" + newRow.Leistung + ";" + newRow.JobDatum.ToString(Extensions.tpDateFormat), false);
                sourceRow.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;
                sourceRow.ClearContents();
                sourceRow.ClearComments();
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyJobevidenzRow: " + ex.Message, true);
            }
        }

        private bool CopyRowToIn(Worksheet sourceSheet, ProjectRow row, string userName, string bereich, string teilBereich) {
            bool retOK = false;
            string workBookName = userName + "je" + Settings.Default.UserInterneJobsFileExtension;
            string fileName = Path.Combine(MaPath, workBookName);
            Workbook wb = null;
            try {
                if (WorkBookIsLoaded(Path.GetFileName(workBookName))) {
                    wb = this.GetWorkbook(Path.GetFileName(workBookName));
                } else {
                    if (File.Exists(fileName) & !Tools.FileIsInUse(fileName)) {
                        wb = exApp.Workbooks.Open(fileName);
                    } else {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, String.Format(Resources.FileIsInUseOrNotExists, fileName), true);
                        if (!CopyRowToJesu(row, userName)) {
                            row.MarkAsFailed();
                        }
                        return false;
                    }
                }
                Worksheet targetSheet = null;
                if (wb != null) {
                    targetSheet = wb.Sheets[userName];
                    if (targetSheet != null) {
                        //int freeRowNumber = GetInFreeRowNumber(targetSheet, bereich, teilBereich);
                        int freeRowNumber = GetJeFreeRowNumber(teilBereich, targetSheet);
                        if (freeRowNumber == 0) {
                            freeRowNumber = GetInFreeRowNumber(targetSheet, bereich, "");
                        }
                        if (freeRowNumber != 0) {
                            Range row1 = targetSheet.Rows[freeRowNumber];
                            Range sourceRow = row.MyRow;

                            sourceRow.Copy();
                            ((Microsoft.Office.Interop.Excel._Worksheet)targetSheet).Activate();
                            row1.Select();
                            row1.PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                            //row1.Font.Name = Extensions.tpFontBold;
                            Extensions.SetFontBoldOrNormal(row1, Extensions.tpFont.Bold);
                            retOK = true;
                        } else {
                            row.MarkAsFailed();
                        }
                    }

                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyRowToIn: " + ex.Message, true);
                row.MarkAsFailed();
            } finally {
                if (wb != null) {
                    wb.Save();
                    wb.Close(true);
                }
            }
            return retOK;
        }

        private bool CopyRowToJesu(ProjectRow row, string userName) {
            //bool ret = CopyRowToFile(row, userName, "Jesu.xlsx");
            return true; // ret;
        }

        private bool CopyRowToLesu(ProjectRow row, string userName) {
            bool ret = CopyRowToFile(row, userName, "Lesu.xlsx");
            if (ret) {
                row.SetExported("Le");
                row.SetFontBold(false);
            }
            return ret;
        }

        private bool CopyRowToFile(ProjectRow row, string userName, string fileName) {

            string path = @"\\TP-S01\Torprofi\Admi\Ma\";
            bool ret = true;
            bool IsLoaded = false;

            #region Check File exists

            if (!File.Exists(Path.Combine(path, fileName))) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyRowToFile: " + String.Format(Resources.FileIsInUseOrNotExists, fileName), true);
                return false;
            }

            #endregion

            Workbook wb;
            if (WorkBookIsLoaded(Path.GetFileName(fileName))) {
                IsLoaded = true;
                wb = this.GetWorkbook(Path.GetFileName(fileName));
            } else {
                wb = exApp.Workbooks.Open(Path.Combine(path, fileName));
            }

            if (!Tools.SheetsExists(wb, userName)) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyRowToFile: User(" + userName + ") nicht vorhanden!", true);
                wb.Close(false);
                return false;
            }

            try {
                Worksheet sh = wb.Sheets[userName];
                int rowNumber = GetFirstFreeRowNumber(3, sh);

                Range r2 = sh.Rows[rowNumber];

                r2.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                r2.PasteSpecial(XlPasteType.xlPasteComments, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyRowToFile: " + ex.Message, true);
                ret = false;
            } finally {
                wb.Save();
                if (!IsLoaded) {
                    wb.Close(false);
                }
            }
            return ret;
        }

        private void DeleteEmptyRows(Worksheet sh, ProjectSheet sheet, int startRow, int lastRow, int columnNumber) {
            for (int i = startRow; i < lastRow; i++) {
                Range r1 = sh.Cells[i, columnNumber];
                if (r1.Value == null) {
                    Range row = sh.Rows[i];
                    row.Delete(Shift: XlDeleteShiftDirection.xlShiftUp);
                    sheet.ZaZeileSummeLeistungen += -1;
                    lastRow += -1;
                }
                if (i >= lastRow) {
                    break;
                }
            }
        }

        public void DeleteInternRow(Worksheet sh, ProjectRow row) {
            int startRow = 3;
            bool weiter = false;
            bool deleted = false;

            while (!weiter & startRow < 5000) {
                Range r1 = sh.Rows[startRow];
                ProjectRow delRow = new ProjectRow(r1);
                Range r2 = sh.Cells[startRow, "B"];
                Range r3 = sh.Cells[startRow, "C"];
                string t1 = Tools.Nz(Convert.ToString(r2.Value), "");
                string t2 = t1 + Tools.Nz(Convert.ToString(r3.Value), "");

                if ((((Workbook)sh.Parent).Name.Contains("le")) & t2.ToUpper().Contains("LEISTUNGEN")) {
                    weiter = true;
                }

                if (!weiter) {
                    if (delRow.Equals(row)) {
                        r1.Delete(Shift: XlDeleteShiftDirection.xlShiftUp);
                        deleted = true;
                        startRow--;
                    }
                }
                startRow++;
            }

            if (!deleted) {
                StringBuilder s1 = new StringBuilder();
                s1.Append("DeleteInternRow : ");
                s1.Append(row.KundenName + " " + row.RowTerm + " ");
                s1.Append(row.JobDatum.ToString(Extensions.tpDateFormat) + " ");
                s1.Append(row.Fahrzeug + " ");
                s1.Append(row.Durchführender1 + " ");
                s1.Append(row.Durchführender2 + " ");
                s1.Append(row.RowLeistung + " ");
                s1.Append("not found!");
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "DeleteInternRow: " + s1.ToString(), true);
            }
        }

        public void ExportAccountData() {

            #region Members

            Workbook wb = null;
            Worksheet sh = null;
            bool weiter = false;
            Range accID = null; ;
            Range accNo = null;
            Range accNa = null;
            Range accDesc = null;
            Range accDet = null;
            Range accText = null;
            Range faGt = null;
            Range faKg = null;
            Range faKp = null;
            Range faKt = null;
            Range faLc = null;
            Range faMh = null;
            Range faMi = null;
            Range faMs = null;
            Range faNa = null;
            Range faPi = null;
            Range faSm = null;
            Range faSv = null;
            Range faTp = null;
            Range faWm = null;
            Range faWp = null;
            Range faZa = null;
            Range faZk = null;
            Range faZv = null;
            Range faEf = null;
            Range faOg = null;

            Range lastRow = null;

            int orgKeyEf = -1;
            int orgKeyOg = -2;
            int accountID = 0;
            int pAccountID = 0;
            int pAccountNumber = 0;
            string pAccountName = null;
            string pDescription = null;
            string pDetails = null;
            string pText = null;

            string fileName = @"T:\Mark\Qs\Ak\80da1.xlsx";
            string sheetSK = "Skneu";
            string sheetPK = "Pkneu";

            #endregion
            
            #region Check File exists or is in use

            if (!File.Exists(fileName)) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "File (" + fileName + ") nicht gefunden!", true);
                return;
            }

            if (Tools.FileIsInUse(fileName)) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "File (" + fileName + ") ist geöffnet!", true);
                return;
            }

            #endregion

            try {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start ExportAccountData", false);
                wb = exApp.Workbooks.Open(fileName);
                sh = wb.Sheets[sheetSK];
                int startrow = 3;

                #region Save Sk

                while (weiter == false) {
                    lastRow = sh.Cells[startrow, "C"];
                    if (lastRow.Value != null) {
                        if (lastRow.Value.ToString().Contains("Summe Konten")) {
                            break;
                        }
                    }

                    accID = sh.Cells[startrow, "A"];
                    accNo = sh.Cells[startrow, "B"];
                    accNa = sh.Cells[startrow, "C"];
                    accDesc = sh.Cells[startrow, "AF"];
                    accDet = sh.Cells[startrow, "AG"];
                    accText = sh.Cells[startrow, "AH"];

                    //if (accID.Value == null || accID.Value.ToString() == "") {
                        //int.TryParse(accID.Value.ToString(), out pAccountID);
                        int.TryParse(accNo.Value.ToString(), out pAccountNumber);
                        if (accNa.Value != null) {
                            pAccountName = Convert.ToString(accNa.Value);
                        }
                        if (accDesc.Value != null) {
                            pDescription = Convert.ToString(accDesc.Value);
                        }
                        if (accDet.Value != null) {
                            pDetails = Convert.ToString(accDet.Value);
                        }
                        if (accText.Value != null) {
                            pText = Convert.ToString(accText.Value);
                        }

                        if (accID.Value == null || accID.Value.ToString() == "") {
                            accountID = TpAccountingDataManager.SaveAccount(pAccountID, pAccountNumber, pAccountName, pDescription, pDetails, pText, true);
                            if (accountID != 0 && (accID.Value == null || accID.Value.ToString() == "")) {
                                accID.Value = accountID;
                            }
                        } else {
                            accountID = Convert.ToInt32(accID.Value);
                        }
                        
                        faGt = sh.Cells[startrow, "I"];
                        faKg = sh.Cells[startrow, "J"];
                        faKp = sh.Cells[startrow, "K"];
                        faKt = sh.Cells[startrow, "L"];
                        faLc = sh.Cells[startrow, "M"];
                        faMh = sh.Cells[startrow, "N"];
                        faMi = sh.Cells[startrow, "O"];
                        faMs = sh.Cells[startrow, "P"];
                        faNa = sh.Cells[startrow, "Q"];
                        faPi = sh.Cells[startrow, "R"];
                        faSm = sh.Cells[startrow, "S"];
                        faSv = sh.Cells[startrow, "T"];
                        faTp = sh.Cells[startrow, "U"];
                        faWm = sh.Cells[startrow, "V"];
                        faWp = sh.Cells[startrow, "W"];
                        faZa = sh.Cells[startrow, "X"];
                        faZk = sh.Cells[startrow, "Y"];
                        faZv = sh.Cells[startrow, "Z"];
                        faEf = sh.Cells[startrow, "AA"];
                        faOg = sh.Cells[startrow, "AB"];

                        int fa = 0;

                        if (faGt.Value != null) {
                            int.TryParse(faGt.Value.ToString(), out fa);
                            if (fa == 1 ) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Gt");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Gt");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faGt.Value = null;
                            }
                        }

                        if (faKg.Value != null) {
                            int.TryParse(faKg.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Kg");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Kg");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faKg.Value = null;
                            }
                        }

                        if (faKp.Value != null) {
                            int.TryParse(faKp.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Kp");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Kp");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faKp.Value = null;
                            }
                        }

                        if (faKt.Value != null) {
                            int.TryParse(faKt.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Kt");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Kt");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faKt.Value = null;
                            }
                        }

                        if (faLc.Value != null) {
                            int.TryParse(faLc.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Lc");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Lc");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faLc.Value = null;
                            }
                        }

                        if (faMh.Value != null) {
                            int.TryParse(faMh.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Mh");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Mh");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faMh.Value = null;
                            }
                        }

                        if (faMi.Value != null) {
                            int.TryParse(faMi.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Mi");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Mi");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faMi.Value = null;
                            }
                        }

                        if (faMs.Value != null) {
                            int.TryParse(faMs.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Ms");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Ms");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faMs.Value = null;
                            }
                        }
                        if (faNa.Value != null) {
                            int.TryParse(faNa.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Na");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Na");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faNa.Value = null;
                            }
                        }

                        if (faPi.Value != null) {
                            int.TryParse(faPi.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Pi");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Pi");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faPi.Value = null;
                            }
                        }

                        if (faSm.Value != null) {
                            int.TryParse(faSm.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Sm");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Sm");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faSm.Value = null;
                            }
                        }

                        if (faSv.Value != null) {
                            int.TryParse(faSv.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Sv");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Sv");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faSv.Value = null;
                            }
                        }

                        if (faTp.Value != null) {
                            int.TryParse(faTp.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Tp");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Tp");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faTp.Value = null;
                            }
                        }

                        if (faWm.Value != null) {
                            int.TryParse(faWm.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Wm");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Wm");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faWm.Value = null;
                            }
                        }

                        if (faWp.Value != null) {
                            int.TryParse(faWp.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Wp");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Wp");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faWp.Value = null;
                            }
                        }

                        if (faZa.Value != null) {
                            int.TryParse(faZa.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Za");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Za");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faZa.Value = null;
                            }
                        }

                        if (faZk.Value != null) {
                            int.TryParse(faZk.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Zk");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Zk");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faZk.Value = null;
                            }
                        }

                        if (faZv.Value != null) {
                            int.TryParse(faZv.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Zv");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                            if (fa == -1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Zv");
                                if (org != null) {
                                    TpAccountingDataManager.UpdateAccount2Company(accountID, org.OrgKey);
                                }
                                faZv.Value = null;
                            }
                        }

                        if (faEf.Value != null) {
                            int.TryParse(faEf.Value.ToString(), out fa);
                            if (fa == 1) {
                                int ret = TpAccountingDataManager.SaveAccount2Company(accountID, orgKeyEf);
                            }
                            if (fa == -1) {
                                TpAccountingDataManager.UpdateAccount2Company(accountID, orgKeyEf);
                                faEf.Value = null;
                            }
                        }

                        if (faOg.Value != null) {
                            int.TryParse(faOg.Value.ToString(), out fa);
                            if (fa == 1) {
                                int ret = TpAccountingDataManager.SaveAccount2Company(accountID, orgKeyOg);
                            }
                            if (fa == -1) {
                                TpAccountingDataManager.UpdateAccount2Company(accountID, orgKeyOg);
                                faOg.Value = null;
                            }
                        }

                    //}
                    startrow++;
                }

                #endregion

                #region Save Pk

                sh = wb.Sheets[sheetPK];
                startrow = 2;
                weiter = false;
                pAccountNumber = 0;
                pAccountID = 0;
                pAccountName = null;

                while (weiter == false) {
                    lastRow = sh.Cells[startrow, "C"];
                    if (lastRow.Value != null) {
                        if (lastRow.Value.ToString() == "Summe") {
                            break;
                        }
                    }

                    accID = sh.Cells[startrow, "A"];
                    accNo = sh.Cells[startrow, "B"];
                    accNa = sh.Cells[startrow, "C"];

                    if ((accID.Value == null || accID.Value.ToString() == "") && !accNa.Value.ToString().Contains("Summe")) {

                        #region Save

                        //int.TryParse(accID.Value.ToString(), out pAccountID);
                        if (accNo.Value != null) {
                            int.TryParse(accNo.Value.ToString(), out pAccountNumber);
                        }
                        if (accNa.Value != null) {
                            pAccountName = Convert.ToString(accNa.Value);
                        }

                        accountID = TpAccountingDataManager.SaveAccount(pAccountID, pAccountNumber, pAccountName, null, null, null, false);

                        faKg = sh.Cells[startrow, "E"];
                        faMh = sh.Cells[startrow, "F"];
                        faMi = sh.Cells[startrow, "G"];
                        faMs = sh.Cells[startrow, "H"];
                        faTp = sh.Cells[startrow, "I"];

                        int fa = 0;

                        if (faKg.Value != null) {
                            int.TryParse(faKg.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Kg");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                        }

                        if (faMh.Value != null) {
                            int.TryParse(faMh.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Mh");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                        }

                        if (faMi.Value != null) {
                            int.TryParse(faMi.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Mi");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                        }

                        if (faMs.Value != null) {
                            int.TryParse(faMs.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Ms");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                        }

                        if (faTp.Value != null) {
                            int.TryParse(faTp.Value.ToString(), out fa);
                            if (fa == 1) {
                                Organisation org = OrganisationDataManager.LoadOrganisation("Tp");
                                if (org != null) {
                                    int ret = TpAccountingDataManager.SaveAccount2Company(accountID, org.OrgKey);
                                }
                            }
                        }

                        #endregion

                    }
                    startrow++;
                }

                #endregion

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "ExportAccountData: " + ex.Message, true);
            } finally {
                if (wb != null) {
                    wb.Save();
                    wb.Close(SaveChanges: true);
                }
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End ExportAccountData", false);
            }

        }

        private void FinalJobDeleteEmptyRows(Worksheet sh) {
            int startRow = 5;
            bool selectRows = false;
            while (selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                string t1 = GetRowDescription(row);
                if (t1 == "") {
                    Range nextRow = sh.Rows[startRow + 1];
                    string t2 = GetRowDescription(nextRow);
                    nextRow = sh.Rows[startRow + 2];
                    t2 += GetRowDescription(nextRow);
                    if (!t2.Contains("Su")) {
                        row.Delete(Shift: XlDeleteShiftDirection.xlShiftUp);
                    } else {
                        row.ClearContents();
                        row.ClearComments();
                        startRow++;
                    }
                    continue;
                }
                if (t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                }
                startRow++;
            }

        }

        private void FinalJobOrderRows(Worksheet sh) {
            FinalJobOrderRows(sh, Extensions.tpSorting.Area, Extensions.tpSortOrder.Up);
        }

        private void FinalJobOrderRows(Worksheet sh, Extensions.tpSorting sortingType, Extensions.tpSortOrder sortOrder) {
            int startRow = 5;
            int firstAreaRow = 5;
            bool selectRows = false;
            while (selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                string t1 = GetRowDescription(row);
                if (t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                    continue;
                }
                if (t1.Contains("Su")) {
                    string[] sortColumns;

                    if (sortingType == Extensions.tpSorting.Area) {
                        sortColumns = Settings.Default.SortColumnsArea.Split(',');
                    } else if (sortingType == Extensions.tpSorting.Date) {
                        sortColumns = Settings.Default.SortColumnsDate.Split(',');
                    } else {
                        sortColumns = Settings.Default.SortColumnsHour.Split(',');
                    }

                    SortSheetRows(sh, firstAreaRow, startRow - 1, sortColumns, sortOrder);
                    firstAreaRow = startRow + 1;
                }
                startRow++;
            }
        }

        private void FinalJobInsertEmptyRows(Worksheet sh) {
            int startRow = 5;
            int emptyRows = 0;
            ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
            bool selectRows = false;
            while (selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                string t1 = GetRowDescription(row);
                if (t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                    continue;
                }
                if (t1 == "") {
                    emptyRows += 1;
                }
                if (t1.Contains("Su")) {
                    if (emptyRows < 3) {
                        for (int i = 0; i < (3 - emptyRows); i++) {
                            Range selRow = sh.Rows[startRow + i - 1];
                            selRow.Select();
                            selRow.Copy();
                            selRow.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                            Range targetRow = sh.Rows[startRow + i];
                            targetRow.ClearContents();
                            targetRow.ClearComments();
                        }
                        startRow += (4 - emptyRows);
                        emptyRows = 0;
                    } else {
                        emptyRows = 0;
                        startRow++;
                    }
                } else {
                    startRow++;
                }
            }
        }

        private void FinalJobSetAllAreasToVisible(Worksheet sh) {
            int startRow = 5;
            ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
            bool selectRows = false;
            while (selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                string t1 = GetRowDescription(row);
                if (t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                    continue;
                }
                Range r1 = sh.Rows[startRow];
                if (r1.EntireRow.Hidden) {
                    r1.EntireRow.Hidden = false;

                }
                startRow++;
            }
        }

        private void FinalJobSetEmptyAreasToHidden(Worksheet sh) {
            int startRow = 5;
            int firstAreaRow = 5;
            int emptyRows = 0;
            ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
            bool selectRows = false;
            while (selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                string t1 = GetRowDescription(row);
                if (t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                    continue;
                }
                if (t1 == "") {
                    emptyRows += 1;
                }
                if (t1.Contains("Su")) {
                    if ((startRow - firstAreaRow) == emptyRows) {
                        Range r1 = sh.Rows[firstAreaRow.ToString() + ":" + (startRow - 1).ToString()];
                        r1.EntireRow.Hidden = true;
                    }
                    firstAreaRow = startRow + 1;
                    emptyRows = 0;
                }
                startRow++;
            }
        }

        private void FinalJobSetFontAndFormulas(Worksheet sh, DateTime showFrom, DateTime showTo) {
            FinalJobSetFontAndFormulas(sh, showFrom, showTo, Extensions.tpContent.AllJobs);
        }

        private void FinalJobSetFontAndFormulas(Worksheet sh, DateTime showFrom, DateTime showTo, Extensions.tpContent contentType) {
            int startRow = 5;
            int jobRows = 0;
            ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
            bool selectRows = false;
            while (selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                if (row == null) {
                    continue;
                }
                string t1 = GetRowDescription(row);
                if (t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                    continue;
                }
                if (t1.ToUpper().Contains("SU")) {
                    Range r2 = sh.Cells[startRow, "H"];
                    r2.Value = jobRows;
                    jobRows = 0;
                    startRow++;
                    continue;
                }

                ProjectRow myRow = new ProjectRow(row);
                bool jobDateIsReady = (myRow.JobDatum >= showFrom) & (myRow.JobDatum <= showTo);
                bool isProjectRow = myRow.Spalte("AI").ToString() != "";

                row.EntireRow.Hidden = true;
                if (contentType == Extensions.tpContent.AllJobs & jobDateIsReady) {
                    row.EntireRow.Hidden = false;
                } else if (contentType == Extensions.tpContent.InternJobs & jobDateIsReady & !isProjectRow) {
                    row.EntireRow.Hidden = false;
                } else if (contentType == Extensions.tpContent.ProjectJobs & jobDateIsReady & isProjectRow) {
                    row.EntireRow.Hidden = false;
                }

                SetFormula(startRow, sh, myRow, true);
                //Extensions.tpFont tpf = (row.Font.Bold ? Extensions.tpFont.Bold : Extensions.tpFont.Regular);
                //tpf = (row.EntireRow.Hidden ? tpf : Extensions.tpFont.Bold);
                //Extensions.SetFontBoldOrNormal(row, tpf);
                row.Font.Bold = (row.EntireRow.Hidden ? row.Font.Bold : true);
                jobRows += (row.EntireRow.Hidden ? 0 : 1);
                startRow++;
            }
        }

        public void FinalJobsForInternFiles() {
            int endRow = 0;
            Workbook wb = null;
            string currentFile = "";
            string search = "K, P";
            string[] columns = search.Split(',');

            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "FinalJobsForInternFiles: Start", false);

            string[] myFiles = Directory.GetFiles(MaPath, Settings.Default.FilesInternSearchString);
            try {
                foreach (string myFile in myFiles) {
                    currentFile = myFile;
                    if (Tools.FileIsInUse(myFile) | (!File.Exists(myFile))) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, String.Format(Resources.FileIsInUseOrNotExists, myFile), true);
                        continue;
                    }

                    if (WorkBookIsLoaded(Path.GetFileName(myFile))) {
                        wb = this.GetWorkbook(Path.GetFileName(myFile));
                    } else {
                        if (File.Exists(myFile) & !Tools.FileIsInUse(myFile)) {
                            wb = exApp.Workbooks.Open(myFile);
                        }
                    }
                    if (wb.Name.Contains("je") & wb.Name.Contains(".xlsm")) {
                        Worksheet sh = wb.Sheets[wb.Name.Left(2)];
                        ClearJobSheet(sh, exApp, out endRow);
                        SheetSort(sh, 3, endRow - 1, columns, TpExtensions.Extensions.tpSortOrder.Up);
                        SheetGroupByWeek(sh);
                    }
                    if (wb != null) {
                        wb.Save();
                        wb.Close(SaveChanges: true);
                    }
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "FinalJobsForInternFiles: " + myFile, false);
                }

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "FinalJobsForInternFiles: (" + currentFile + ") " + ex.Message, true);
            }
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "FinalJobForInternFiles: Ende", false);
        }

        private int FindCheckRow(object toFind, Range selRange, Range activeCell) {
            Range r1;
            r1 = selRange.Find(toFind, activeCell, SearchDirection: XlSearchDirection.xlNext);
            if (r1 != null) {
                return r1.Row;
            }
            return 0;
        }

        private string GetTargetSheetName(string defaultName, string targetBookName, string fileSuffix) {
            if (fileSuffix == "je") {
                defaultName = "In";
            }
            if (targetBookName.Contains("Fehler")) {
                defaultName = (fileSuffix == "je" ? "Jobevidenzen" : "Leistungen");
            }
            return defaultName;
        }

        private void KorrekturCheckFile(Workbook wb) {
            try {
                foreach (Worksheet sh in wb.Worksheets) {
                    if (sh.Name.IsNumeric(System.Globalization.NumberStyles.Any)) {
                        object toFind = "S";
                        Range r1 = sh.Cells[1, "AY"];
                        Range r2 = sh.Columns[r1.Column];
                        if (FindCheckRow(toFind, r2, r1) > 0) {
                            KorrekturMakeStorno(sh, wb.Name.Left(2));
                        }
                        toFind = "K";
                        if (FindCheckRow(toFind, r2, r1) > 0) {
                            KorrekturMakeChanges(sh, wb.Name.Left(2));
                        }

                    }
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturCheckFile: " + ex.Message, true);
            }
        }

        private void KorrekturMakeStorno(Worksheet sh, string d1) {
            bool weiter = false;
            object toFind = "S";
            Range r1 = sh.Cells[3, "AY"];
            Range r2 = sh.Columns[r1.Column];
            while (!weiter) {
                ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                int rowNumber = FindCheckRow(toFind, r2, r1);
                if (rowNumber == 0) {
                    return;
                }
                Range row = sh.Rows[rowNumber];
                ProjectRow stornoRow = new ProjectRow(row);
                stornoRow.ChangeType = "W";
                if (stornoRow.IsProjectJob) {
                    KorrekturProjectFile(d1, stornoRow, "S");
                } else {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturMakeStorno: Zeiloe ist keine Project-Zeile (" + stornoRow.KundenName + "/" + stornoRow.ProjectName + ")", true);
                }
                rowNumber = 0;
            }
        }

        private void KorrekturMakeChanges(Worksheet sh, string d1) {
            bool weiter = false;
            object toFind = "K";
            Range r1 = sh.Cells[3, "AY"];
            Range r2 = sh.Columns[r1.Column];
            while (!weiter) {
                ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                int rowNumber = FindCheckRow(toFind, r2, r1);
                if (rowNumber == 0) {
                    return;
                }
                Range row = sh.Rows[rowNumber];
                ProjectRow changeRow = new ProjectRow(row);

                changeRow.ChangeType = "W";
                if (changeRow.IsProjectJob) {
                    KorrekturProjectFile(d1, changeRow, "K");
                } else {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturMakeChanges: Zeiloe ist keine Project-Zeile (" + changeRow.KundenName + "/" + changeRow.ProjectName + ")", true);
                }

            }
        }

        private void KorrekturNnle() {
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start KorrekturNnLe", false);
            string currentFile = "";
            try {

                string[] myFiles = Directory.GetFiles(MaPath, Settings.Default.FilesNnle);
                foreach (string fileName in myFiles) {
                    currentFile = fileName;
                    if (File.Exists(fileName) & !Tools.FileIsInUse(fileName)) {
                        Workbook wb = exApp.Workbooks.Open(fileName);
                        KorrekturCheckFile(wb);
                        wb.Save();
                        wb.Close();
                    } else {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturNnLe: " + String.Format(Resources.FileIsInUseOrNotExists, fileName), true);
                        continue;
                    }
                }

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturNnLe: (" + currentFile + "): " + ex.Message, true);
            }
        }

        private void KorrekturProjectFile(string d1, ProjectRow row, string kor) {

            string filePath = Settings.Default.CustomerFilePath + row.ProjectName.Left(2) + @"\" + row.KundenName.Left(1) + @"\";
            string fileName = row.KundenName + row.ProjectName.Substring(0, 4) + ".xlsm";
            if (!File.Exists(Path.Combine(filePath + fileName))) {
                filePath = Settings.Default.SalesLeadPath + row.KundenName.Left(1) + @"\";
            }
            if (!File.Exists(Path.Combine(filePath + fileName))) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturProjektFile(" + fileName + ") nicht vorhanden!", true);
                return;
            }
            Workbook wb;
            if (WorkBookIsLoaded(Path.GetFileName(fileName))) {
                wb = this.GetWorkbook(Path.GetFileName(fileName));
            } else {
                wb = exApp.Workbooks.Open(Path.Combine(filePath + fileName));
            }
            string sheetName = (string)row.Spalte("D");
            if (!sheetName.Contains("p")) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturProjektFile:ProjektNamr nicht korrekt. " + row.KundenName + "/" + row.Spalte("D").ToString(), true);
                wb.Close(false);
                return;
            }
            int lenStr = sheetName.Length;
            int posStr = sheetName.IndexOf("p");
            sheetName = sheetName.Right(lenStr - posStr).ToUpper() + "r";
            if (!Tools.SheetsExists(wb, sheetName)) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturProjectFile:Arbeitsblatt (" + sheetName + ")nicht vorhanden. " + row.KundenName + "/" + row.Spalte("D").ToString(), true);
                wb.Close(false);
                return;
            }
            try {
                Worksheet sh = wb.Sheets[sheetName];
                int rowNumber = 301;
                bool weiter = false;
                while (!weiter) {
                    Range selRow = sh.Rows[rowNumber];
                    ProjectRow targetRow = new ProjectRow(selRow);

                    if (targetRow.KundenName == row.KundenName &
                        targetRow.ProjectName == row.ProjectName &
                        targetRow.JobDatum == row.JobDatum &
                        targetRow.JobTime == row.JobTime &
                        targetRow.RowType == row.RowType &
                        targetRow.Fahrzeug == row.Fahrzeug &
                        targetRow.Durchführender1 == row.Durchführender1 &
                        targetRow.Durchführender2 == row.Durchführender2 &
                        targetRow.Leistung == row.Leistung
                        ) {
                        if (row.Durchführender1 != d1) {
                            LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturProjektFile: Durchführender ist nicht D1", true);
                            continue;
                        }
                        if (kor.ToUpper() == "K") {

                            targetRow.JobDatum = (DateTime)(row.ChangeDate != new DateTime(1900, 1, 1) ? row.ChangeDate : targetRow.JobDatum);
                            targetRow.JobTime = (row.ChangeBeginTime != 0 ? row.ChangeBeginTime : targetRow.JobTime);
                            targetRow.JobEndTime = (row.ChangeEndTime != 0 ? row.ChangeEndTime : targetRow.JobEndTime);
                            if (targetRow.IsPauschalTime) {
                                targetRow.PauschalZeit = (row.ChangeFlatTime != 0 ? row.ChangeFlatTime : targetRow.PauschalZeit);
                            }
                            targetRow.LeistungProzent = (row.ChangePerformance != 0 ? row.ChangePerformance : targetRow.LeistungProzent);
                            targetRow.Menge = (row.Menge != 0 ? row.Menge : targetRow.Menge);
                            if (row.Durchführender2 != "") {
                                KorrekturMitarbeiterRow(row, row.Durchführender2 + wb.Name.Substring(2) + ".xlsx", row.JobDatum.Month.ToString("00"), kor);
                            }
                            row.JobDatum = (DateTime)(row.ChangeDate != new DateTime(1900, 1, 1) ? row.ChangeDate : row.JobDatum);
                            row.JobTime = (row.ChangeBeginTime != 0 ? row.ChangeBeginTime : row.JobTime);
                            row.JobEndTime = (row.ChangeEndTime != 0 ? row.ChangeEndTime : row.JobEndTime);
                            row.PauschalZeit = (row.ChangeFlatTime != 0 ? row.ChangeFlatTime : row.PauschalZeit);
                            row.LeistungZeitSumme = (row.ChangeFlatTime != 0 ? row.ChangeFlatTime : row.LeistungZeitSumme);
                            row.LeistungProzent = (row.ChangePerformance != 0 ? row.ChangePerformance : row.LeistungProzent);
                            row.Menge = (row.Menge != 0 ? row.Menge : row.Menge);
                            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "KorrekturProjectFile: Row wurde geändert: " + row.KundenName + "/" + row.ProjectName + "/" + row.Leistung, false);
                            ResetChanges(row);
                            weiter = true;
                        }
                        if (kor.ToUpper() == "S") {
                            targetRow.Durchführender1 = "";
                            targetRow.Durchführender2 = "";
                            targetRow.Fahrzeug = "";
                            targetRow.JobDatum = new DateTime(1900, 1, 1);
                            targetRow.JobTime = 0;
                            targetRow.JobEndTime = 0;
                            targetRow.SetExported("");
                            if (row.Durchführender2 != "") {
                                KorrekturMitarbeiterRow(row, row.Durchführender2 + "le" + row.JobDatum.Year.ToString().Right(2) + ".xlsx", row.JobDatum.Month.ToString("00"), kor);
                            }
                            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "KorrekturProjectFile: Row wurde storniert:" + row.KundenName + "/" + row.ProjectName + "/" + row.Leistung, false);
                            Range delRow = row.MyRow;
                            delRow.Delete(XlDeleteShiftDirection.xlShiftUp);
                            weiter = true;
                        }
                        wb.Save();
                    }
                    rowNumber += 1;
                    if (!weiter & (rowNumber == 10000 | targetRow.RowType == "Db")) {
                        weiter = true;
                    }

                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturProjectFile: " + ex.Message, true);
            } finally {
                wb.Close(true);
            }
        }

        private void KorrekturMitarbeiterRow(ProjectRow row, string ma, string monat, string kor) {
            string fileName = Path.Combine(MaPath, ma);
            if (!File.Exists(Path.Combine(fileName))) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturMitarbeiterRow: File (" + fileName + ") ist nicht vorhanden!", true);
                return;
            }
            Workbook wb;
            if (WorkBookIsLoaded(Path.GetFileName(ma))) {
                wb = this.GetWorkbook(Path.GetFileName(ma));
            } else {
                wb = exApp.Workbooks.Open(fileName);
            }
            Worksheet sh = wb.Sheets[monat];
            int rowNumber = 3;
            Range r1;
            ProjectRow targetRow;
            bool weiter = false;
            while (!weiter) {
                r1 = sh.Rows[rowNumber];
                targetRow = new ProjectRow(r1);
                if (targetRow.KundenName.Contains("Summe")) {
                    wb.Save();
                    wb.Close();
                    return;
                }
                try {
                    if (targetRow.KundenName == row.KundenName &
                            targetRow.ProjectName == row.ProjectName &
                            targetRow.JobDatum == row.JobDatum &
                            targetRow.JobTime == row.JobTime &
                            targetRow.RowType == row.RowType &
                            targetRow.Fahrzeug == row.Fahrzeug &
                            targetRow.Durchführender1 == row.Durchführender1 &
                            targetRow.Durchführender2 == row.Durchführender2 &
                            targetRow.Leistung == row.Leistung
                            ) {
                        if (kor == "K") {
                            targetRow.JobDatum = (DateTime)(row.ChangeDate != new DateTime(1900, 1, 1) ? row.ChangeDate : targetRow.JobDatum);
                            targetRow.JobTime = (row.ChangeBeginTime != 0 ? row.ChangeBeginTime : targetRow.JobTime);
                            targetRow.JobEndTime = (row.ChangeEndTime != 0 ? row.ChangeEndTime : targetRow.JobEndTime);
                            targetRow.PauschalZeit = (row.ChangeFlatTime != 0 ? row.ChangeFlatTime : targetRow.PauschalZeit);
                            targetRow.LeistungZeitSumme = (row.ChangeFlatTime != 0 ? row.ChangeFlatTime : targetRow.LeistungZeitSumme);
                            targetRow.LeistungProzent = (row.ChangePerformance != 0 ? row.ChangePerformance : targetRow.LeistungProzent);
                            targetRow.Menge = (row.Menge != 0 ? row.Menge : targetRow.Menge);
                        }
                        if (kor == "S") {
                            r1.Delete(XlDeleteShiftDirection.xlShiftUp);
                        }
                        wb.Save();
                        wb.Close();
                        return;
                    }
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KorrekturMitarbeiterRow: " + ex.Message, true);
                }
                rowNumber += 1;
            }
        }

        private void ResetChanges(ProjectRow row) {
            Range r1 = row.MyRow.Cells[1, "AY"];
            r1.Value = "";
            r1 = row.MyRow.Cells[1, "AZ"];
            r1.Value = "";
            r1 = row.MyRow.Cells[1, "BA"];
            r1.Value = "";
            r1 = row.MyRow.Cells[1, "BB"];
            r1.Value = "";
            r1 = row.MyRow.Cells[1, "BC"];
            r1.Value = "";
            r1 = row.MyRow.Cells[1, "BD"];
            r1.Value = "";

        }

        private void Monatsabschluss() {
            Workbook wb = null;
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start Monatsabschluss", false);
            try {
                string[] myFiles = Directory.GetFiles(MaPath, Settings.Default.FilesZaSearchString);

                foreach (string file in myFiles) {
                    try {
                        if (File.Exists(file) & !Tools.FileIsInUse(file)) {
                            wb = exApp.Workbooks.Open(file);
                            Worksheet sh = wb.Sheets[DateTime.Today.AddMonths(-1).Month.ToString("00")];
                            ProjectSheet sheet = new ProjectSheet(sh, true);

                            DeleteEmptyRows(sh, sheet, sheet.ZaZeileSummeArbeitszeit + 1, sheet.ZaZeileSummeLeistungen, 3);

                            string[] sortColumns = new string[2] { "K", "P" };
                            SortSheetRows(sh, sheet.ZaZeileSummeArbeitszeit + 1, sheet.ZaZeileSummeLeistungen - 1, sortColumns);
                        }
                    } catch (Exception ex) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Monatsabschluss.File: (" + file + "): " + ex.Message, true);
                    }
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Monatsabschluss: " + ex.Message, true);
            } finally {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End Monatsabschluss", false);
                if (wb != null) {
                    wb.Save();
                    wb.Close(true);
                }
            }
        }

        private void OutputToScreen(string message) {
            Console.WriteLine(message);
        }

        private void RemoveCustomerData(Range row) {
            Range r1 = row.Cells[1, "E"];
            r1.Value = String.Empty;

            r1 = row.Cells[1, "F"];
            r1.Value = String.Empty;

            r1 = row.Cells[1, "G"];
            r1.Value = String.Empty;

            r1 = row.Cells[1, "H"];
            r1.Value = String.Empty;
        }

        private void RepairHyperlink(Worksheet sh, int sourceRow, string sourceCol, string targetCol) {
            Range c1 = sh.Cells[sourceRow, sourceCol];
            if (c1.Value != null) {
                Range r1 = sh.Cells[sourceRow, targetCol];
                if (r1.Hyperlinks.Count > 0) {
                    r1.Hyperlinks.Delete();
                }
                r1.Hyperlinks.Add(r1, c1.Value.ToString(), Path.GetFileNameWithoutExtension(c1.Value.ToString()));
            }
        }

        public void Run() {
            Run(CallNumber.All);
        }

        public void Run(CallNumber callFunction) {

            try {
                if (exApp == null) {
                    return;
                }
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Run: MaPath: (" + MaPath + "), Jesu: (" + Jesu + "), Lesu: (" + Lesu + ")", false);

                SetApplication(exApp, false);

                if (callFunction == CallNumber.All && !DebugMode) {
                    SetBackup(MaPath, false);
                }

                if (callFunction == CallNumber.Aepü | callFunction == CallNumber.All) {
                    UpdateAe();
                }

                if (callFunction == CallNumber.UpdateJobevidenz | callFunction == CallNumber.All) {
                    UpdateJobEvidenz();
                }

                if (callFunction == CallNumber.UpdateJobEvidenzInternBulk | callFunction == CallNumber.All) {
                    UpdateJobEvidenzIntern(null, true);
                }

                if (callFunction == CallNumber.UpdateLeistungen | callFunction == CallNumber.All) {
                    UpdateLeistungen(DateTime.Today.Year);
                }

                if (callFunction == CallNumber.UpdateJobsFromTecOffice | callFunction == CallNumber.All) {
                    UpdateLeistungenFromTecOffice();
                }

                if (callFunction == CallNumber.UpdateLeistungenInternBulk | callFunction == CallNumber.All) {
                    UpdateLeistungenIntern(null, true);
                }

                if ((callFunction == CallNumber.Monatsabschluss | callFunction == CallNumber.All) & DateTime.Today.Day == Settings.Default.StartDayForMonatsabschluss) {
                    //Monatsabschluss();
                }

                if (callFunction == CallNumber.KorrekturNnle | callFunction == CallNumber.All) {
                    //KorrekturNnle();
                }

                if (callFunction == CallNumber.UpdateJobKataloge | callFunction == CallNumber.All) {
                    //KorrekturNnle();
                }

                if (callFunction == CallNumber.CheckLeistungen | callFunction == CallNumber.All) {
                    CheckLeistungsZeilen(null);
                }

                if (DateTime.Today.Month < 3) {
                    if (callFunction == CallNumber.UpdateLeistungen | callFunction == CallNumber.All) {
                        //UpdateLeistungen(DateTime.Today.AddYears(-1).Year);
                    }
                }

                if (callFunction == CallNumber.ExportAccountData | callFunction == CallNumber.All) {
                    ExportAccountData();
                }

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Run: " + ex.Message, true);
            } finally {
                SetApplication(exApp, true);
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Excel is started: " + (excelIsStarted ? "Yes" : "No"), false);
                if (!excelIsStarted) {
                    exApp.Quit();
                }
            }
        }

        private void SetBackup(string sourcePath, bool overwrite) {
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start SetBackup", false);
            string[] fileTypes = Settings.Default.FileSearchPatterns.Split(',');

            string bupPath = Settings.Default.BackupPath + DateTime.Today.AddDays(-1).Year.ToString().Right(2) + @"\" + DateTime.Today.ToString("yyyyMMdd") + @"\";
            if (!Directory.Exists(bupPath)) {
                Directory.CreateDirectory(bupPath);
            }
            DirectoryInfo source = new DirectoryInfo(sourcePath);
            foreach (string searchString in fileTypes) {
                foreach (FileInfo fi in source.GetFiles(searchString)) {
                    try {
                        if (File.Exists(Path.Combine(bupPath, fi.Name))) {
                            File.Delete(Path.Combine(bupPath, fi.Name));
                        }
                        fi.CopyTo(Path.Combine(bupPath, fi.Name), overwrite);
                    } catch (Exception ex) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SetBackup: " + ex.Message, true);
                    }
                }
            }
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End SetBackup", false);
        }

        private void SetFormula(int startRow, Worksheet sh, ProjectRow row, bool setFinalJobs) {
            Range r1 = sh.Cells[startRow, "AV"];
            Range r3 = sh.Cells[startRow, "Q"];
            Range r4 = sh.Cells[startRow, "P"];
            if (r3.Value != null & r4.Value != null) {
                //if (((string)r1.FormulaR1C1).Contains("*")) {
                //    r1.FormulaR1C1 = "=(R" + startRow.ToString() + "C17-R" + startRow.ToString() + "C16)*" + (setFinalJobs ? (row.MyRow.EntireRow.Hidden ? "0" : "24") : "24");
                //}
                if (((string)r1.FormulaR1C1).Contains("*")) {
                    r1.Value = "=(Q" + startRow.ToString() + "-P" + startRow.ToString() + ")*" + (setFinalJobs ? (row.MyRow.EntireRow.Hidden ? "0" : "24") : "24");
                }
            }
        }

        private void SetInternRow(string fileSuffix, ProjectRow row, string userName) {
            Range sourceRow = row.MyRow;
            SetPattern(sourceRow.Cells[1, "K"], (int)XlPattern.xlPatternNone);
            SetPattern(sourceRow.Cells[1, "U"], (int)XlPattern.xlPatternNone);
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Update " + (fileSuffix == "je" ? "Jobevidenzen" : "Leistungen") + ": " + row.KundenName + row.Spalte("D") + ", " + userName + ";" + row.Leistung + ";" + row.JobDatum.ToString(Extensions.tpDateFormat), false);
            if (fileSuffix != "je") {
                sourceRow.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;
            } else {
                //sourceRow.Font.Name = Extensions.tpFontNormal;
                RemoveCustomerData(sourceRow);
                Extensions.SetFontBoldOrNormal(sourceRow, Extensions.tpFont.Regular);
            }

        }

        private void SetPattern(Range r1, int xlPattern) {
            r1.Interior.Pattern = xlPattern;
        }

        private void SetRowSettings(Range targetRow, string hyperLink) {
            Range r3 = targetRow.Cells[1, "C"];
            AddHyperlink(r3, hyperLink);
            r3 = targetRow.Cells[1, "AE"];
            r3.Value = "Je";
            r3 = targetRow.Cells[1, "H"];
            r3.Value = "";
        }

        public void SheetGroupByD2(Worksheet sh) {
            int startRow = 3;
            int groupStartRow = 3;
            int summenColumn = 48;
            int rowsPerWeek = 0;
            int anzJobs = 0;
            Range row1 = sh.Rows[startRow, Missing.Value];
            Range row2 = sh.Rows[startRow + 1, Missing.Value];
            UpdateJobSheet(sh, "D2");
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start SheetGroupByD2", false);
            ProjectRow row = new ProjectRow(row1);
            ProjectRow nextRow = new ProjectRow(row2);

            while (startRow < 1000) {
                try {
                    if (row.IsGesamtSummenZeile) {
                        Range rz = sh.Cells[startRow, "H"];
                        rz.Value = anzJobs;
                        rz.Font.Color = Tools.RGB(0, 191, 191);

                        Range rg = sh.Cells[startRow, "AV"];
                        rg.FormulaR1C1 = "=Sum(R3C" + summenColumn.ToString() + ":R" + (startRow - 1).ToString() + "C" + summenColumn.ToString() + ")/2";
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "End SheetGroupByD2", false);
                        return;
                    }

                    if (!row.IsEmpty) {
                        Range cr = sh.Rows[startRow];
                        cr.EntireRow.Hidden = false;

                    }

                    if ((row.Durchführender2 != nextRow.Durchführender2)
                        || nextRow.IsGesamtSummenZeile) {
                        rowsPerWeek += 1;
                        Range r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                        r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeTop].Weight = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].Weight = 1;
                        r1.Font.Name = Extensions.tpFontBold;
                        r1.Font.Bold = true;
                        r1.Interior.Pattern = XlPattern.xlPatternNone;

                        Range r2 = sh.Cells[startRow + 1, "B"];
                        r2.Value = "Su";
                        r2.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r3 = sh.Cells[startRow + 1, "C"];
                        r3.Value = row.Durchführender2;
                        r3.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r4 = sh.Cells[startRow + 1, "H"];
                        r4.NumberFormat = "@";
                        r4.HorizontalAlignment = XlHAlign.xlHAlignRight;
                        r4.Value = rowsPerWeek.ToString();
                        r4.Font.Color = Tools.RGB(0, 191, 191);

                        anzJobs += rowsPerWeek;

                        Range r5 = sh.Cells[startRow + 1, "AV"];
                        r5.FormulaR1C1 = "=Sum(R" + groupStartRow.ToString() + "C" + summenColumn.ToString() + ":R" + startRow.ToString() + "C" + summenColumn.ToString() + ")";
                        rowsPerWeek = 0;
                        startRow += 2;
                        groupStartRow = startRow;
                    } else {
                        rowsPerWeek += 1;
                        startRow += 1;

                    }
                    row1 = sh.Rows[startRow, Missing.Value];
                    row2 = sh.Rows[startRow + 1, Missing.Value];

                    row = new ProjectRow(row1);
                    nextRow = new ProjectRow(row2);
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SheetGroupByD2: " + ex.Message, true);
                }
            }
        }

        public void SheetGroupByLeistung(Worksheet sh) {
            int startRow = 3;
            int groupStartRow = 3;
            int summenColumn = 48;
            int rowsPerWeek = 0;
            int currentWeek;
            int anzJobs = 0;
            Range row1 = sh.Rows[startRow, Missing.Value];
            Range row2 = sh.Rows[startRow + 1, Missing.Value];
            UpdateJobSheet(sh, "Leistung");
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start SheetGroupByLeistung", false);
            ProjectRow row = new ProjectRow(row1);
            ProjectRow nextRow = new ProjectRow(row2);

            DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
            Calendar cal = dfi.Calendar;

            currentWeek = cal.GetWeekOfYear(DateTime.Now, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

            while (startRow < 1000) {
                try {
                    if (row.IsGesamtSummenZeile) {
                        Range rz = sh.Cells[startRow, "H"];
                        rz.Value = anzJobs;
                        rz.Font.Color = Tools.RGB(0, 191, 191);

                        Range rg = sh.Cells[startRow, "AV"];
                        rg.FormulaR1C1 = "=Sum(R3C" + summenColumn.ToString() + ":R" + (startRow - 1).ToString() + "C" + summenColumn.ToString() + ")/2";
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End SheetGroupByLeistung", false);
                        return;
                    }

                    if (!row.IsEmpty) {
                        Range cr = sh.Rows[startRow];
                        cr.EntireRow.Hidden = false;

                    }

                    if ((row.Leistung != nextRow.Leistung) || nextRow.IsGesamtSummenZeile) {
                        rowsPerWeek += 1;
                        Range r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                        r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeTop].Weight = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].Weight = 1;
                        r1.Font.Name = Extensions.tpFontBold;
                        r1.Font.Bold = true;
                        r1.Interior.Pattern = XlPattern.xlPatternNone;

                        Range r2 = sh.Cells[startRow + 1, "B"];
                        r2.NumberFormat = "@";
                        r2.Value = "Su";
                        r2.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r3 = sh.Cells[startRow + 1, "C"];
                        r3.Value = row.Leistung;
                        r3.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r4 = sh.Cells[startRow + 1, "H"];
                        r4.NumberFormat = "@";
                        r4.HorizontalAlignment = XlHAlign.xlHAlignRight;
                        r4.Value = rowsPerWeek.ToString();
                        r4.Font.Color = Tools.RGB(0, 191, 191);

                        anzJobs += rowsPerWeek;

                        Range r5 = sh.Cells[startRow + 1, "AV"];
                        r5.FormulaR1C1 = "=Sum(R" + groupStartRow.ToString() + "C" + summenColumn.ToString() + ":R" + startRow.ToString() + "C" + summenColumn.ToString() + ")";
                        rowsPerWeek = 0;
                        startRow += 2;
                        groupStartRow = startRow;
                    } else {
                        rowsPerWeek += 1;
                        startRow += 1;

                    }
                    row1 = sh.Rows[startRow, Missing.Value];
                    row2 = sh.Rows[startRow + 1, Missing.Value];

                    row = new ProjectRow(row1);
                    nextRow = new ProjectRow(row2);
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SheetGroupByLeistung: " + ex.Message, true);
                }
            }
        }

        public void SheetGroupByOrganisation(Worksheet sh) {
            int startRow = 3;
            int groupStartRow = 3;
            int summenColumn = 48;
            int rowsPerWeek = 0;
            int currentWeek;
            int anzJobs = 0;
            Range row1 = sh.Rows[startRow, Missing.Value];
            Range row2 = sh.Rows[startRow + 1, Missing.Value];
            UpdateJobSheet(sh, "Org, Per");
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start SheetGroupByOrganisation", false);
            ProjectRow row = new ProjectRow(row1);
            ProjectRow nextRow = new ProjectRow(row2);

            DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
            Calendar cal = dfi.Calendar;

            currentWeek = cal.GetWeekOfYear(DateTime.Now, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

            while (startRow < 1000) {
                try {
                    if (row.IsGesamtSummenZeile) {
                        Range rz = sh.Cells[startRow, "H"];
                        rz.Value = anzJobs;
                        rz.Font.Color = Tools.RGB(0, 191, 191);

                        Range rg = sh.Cells[startRow, "AV"];
                        rg.FormulaR1C1 = "=Sum(R3C" + summenColumn.ToString() + ":R" + (startRow - 1).ToString() + "C" + summenColumn.ToString() + ")/2";
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End SheetGroupByOrganisation", false);
                        return;
                    }

                    if (!row.IsEmpty) {
                        Range cr = sh.Rows[startRow];
                        cr.EntireRow.Hidden = false;

                    }

                    if ((row.Organisation != nextRow.Organisation) || nextRow.IsGesamtSummenZeile) {
                        rowsPerWeek += 1;
                        Range r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                        r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeTop].Weight = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].Weight = 1;
                        r1.Font.Name = Extensions.tpFontBold;
                        r1.Font.Bold = true;
                        r1.Interior.Pattern = XlPattern.xlPatternNone;

                        Range r2 = sh.Cells[startRow + 1, "B"];
                        r2.NumberFormat = "@";
                        r2.Value = "Su";
                        r2.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r3 = sh.Cells[startRow + 1, "C"];
                        r3.Value = row.Spalte("W");
                        r3.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r4 = sh.Cells[startRow + 1, "H"];
                        r4.NumberFormat = "@";
                        r4.HorizontalAlignment = XlHAlign.xlHAlignRight;
                        r4.Value = rowsPerWeek.ToString();
                        r4.Font.Color = Tools.RGB(0, 191, 191);

                        anzJobs += rowsPerWeek;

                        Range r5 = sh.Cells[startRow + 1, "AV"];
                        r5.FormulaR1C1 = "=Sum(R" + groupStartRow.ToString() + "C" + summenColumn.ToString() + ":R" + startRow.ToString() + "C" + summenColumn.ToString() + ")";
                        rowsPerWeek = 0;
                        startRow += 2;
                        groupStartRow = startRow;
                    } else {
                        rowsPerWeek += 1;
                        startRow += 1;

                    }
                    row1 = sh.Rows[startRow, Missing.Value];
                    row2 = sh.Rows[startRow + 1, Missing.Value];

                    row = new ProjectRow(row1);
                    nextRow = new ProjectRow(row2);
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SheetGroupByOrganisation: " + ex.Message, true);
                }
            }
        }

        public void SheetGroupByTorprofi(Worksheet sh) {
            int startRow = 3;
            int groupStartRow = 3;
            int summenColumn = 48;
            int rowsPerWeek = 0;
            int anzJobs = 0;
            Range row1 = sh.Rows[startRow, Missing.Value];
            Range row2 = sh.Rows[startRow + 1, Missing.Value];
            UpdateJobSheet(sh, "Torprofi");
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start SheetGroupByTorprofi", false);
            ProjectRow row = new ProjectRow(row1);
            ProjectRow nextRow = new ProjectRow(row2);

            while (startRow < 1000) {
                try {
                    if (row.IsGesamtSummenZeile) {
                        Range rz = sh.Cells[startRow, "H"];
                        rz.Value = anzJobs;
                        rz.Font.Color = Tools.RGB(0, 191, 191);

                        Range rg = sh.Cells[startRow, "AV"];
                        rg.FormulaR1C1 = "=Sum(R3C" + summenColumn.ToString() + ":R" + (startRow - 1).ToString() + "C" + summenColumn.ToString() + ")/2";
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End SheetGroupByTorprofi", false);
                        return;
                    }

                    if (!row.IsEmpty) {
                        Range cr = sh.Rows[startRow];
                        cr.EntireRow.Hidden = false;

                    }

                    if ((row.Bereich == nextRow.Bereich
                        && row.Teilbereich != nextRow.Teilbereich)
                        || (row.Bereich != nextRow.Bereich)
                        || nextRow.IsGesamtSummenZeile) {
                        rowsPerWeek += 1;
                        Range r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                        r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeTop].Weight = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].Weight = 1;
                        r1.Font.Name = Extensions.tpFontBold;
                        r1.Font.Bold = true;
                        r1.Interior.Pattern = XlPattern.xlPatternNone;

                        Range r2 = sh.Cells[startRow + 1, "B"];
                        r2.Value = "Su";
                        r2.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r3 = sh.Cells[startRow + 1, "C"];
                        r3.Value = row.Bereich;
                        r3.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r31 = sh.Cells[startRow + 1, "D"];
                        r31.Value = row.Teilbereich;

                        Range r4 = sh.Cells[startRow + 1, "H"];
                        r4.NumberFormat = "@";
                        r4.HorizontalAlignment = XlHAlign.xlHAlignRight;
                        r4.Value = rowsPerWeek.ToString();
                        r4.Font.Color = Tools.RGB(0, 191, 191);

                        anzJobs += rowsPerWeek;

                        Range r5 = sh.Cells[startRow + 1, "AV"];
                        r5.FormulaR1C1 = "=Sum(R" + groupStartRow.ToString() + "C" + summenColumn.ToString() + ":R" + startRow.ToString() + "C" + summenColumn.ToString() + ")";
                        rowsPerWeek = 0;
                        startRow += 2;
                        groupStartRow = startRow;
                    } else {
                        rowsPerWeek += 1;
                        startRow += 1;

                    }
                    row1 = sh.Rows[startRow, Missing.Value];
                    row2 = sh.Rows[startRow + 1, Missing.Value];

                    row = new ProjectRow(row1);
                    nextRow = new ProjectRow(row2);
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SheetGroupByTorprofi: " + ex.Message, true);
                }
            }
        }

        public void SheetGroupByUser(Worksheet sh) {
            int startRow = 3;
            int groupStartRow = 3;
            int summenColumn = 48;
            int rowsPerWeek = 0;
            int anzJobs = 0;
            Range row1 = sh.Rows[startRow, Missing.Value];
            Range row2 = sh.Rows[startRow + 1, Missing.Value];
            UpdateJobSheet(sh, "User");
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start SheetGroupByUser", false);
            ProjectRow row = new ProjectRow(row1);
            ProjectRow nextRow = new ProjectRow(row2);

            while (startRow < 1000) {
                try {
                    if (row.IsGesamtSummenZeile) {
                        Range rz = sh.Cells[startRow, "H"];
                        rz.Value = anzJobs;
                        rz.Font.Color = Tools.RGB(0, 191, 191);

                        Range rg = sh.Cells[startRow, "AV"];
                        rg.FormulaR1C1 = "=Sum(R3C" + summenColumn.ToString() + ":R" + (startRow - 1).ToString() + "C" + summenColumn.ToString() + ")/2";
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End SheetGroupByUser", false);
                        return;
                    }

                    if (!row.IsEmpty) {
                        Range cr = sh.Rows[startRow];
                        cr.EntireRow.Hidden = false;

                    }

                    if ((row.Firma != nextRow.Firma)
                        || nextRow.IsGesamtSummenZeile) {
                        rowsPerWeek += 1;
                        Range r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                        r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeTop].Weight = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].Weight = 1;
                        r1.Font.Name = Extensions.tpFontBold;
                        r1.Font.Bold = true;
                        r1.Interior.Pattern = XlPattern.xlPatternNone;

                        Range r2 = sh.Cells[startRow + 1, "B"];
                        r2.Value = "Su";
                        r2.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r3 = sh.Cells[startRow + 1, "C"];
                        r3.Value = row.Firma;
                        r3.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r4 = sh.Cells[startRow + 1, "H"];
                        r4.NumberFormat = "@";
                        r4.HorizontalAlignment = XlHAlign.xlHAlignRight;
                        r4.Value = rowsPerWeek.ToString();
                        r4.Font.Color = Tools.RGB(0, 191, 191);

                        anzJobs += rowsPerWeek;

                        Range r5 = sh.Cells[startRow + 1, "AV"];
                        r5.FormulaR1C1 = "=Sum(R" + groupStartRow.ToString() + "C" + summenColumn.ToString() + ":R" + startRow.ToString() + "C" + summenColumn.ToString() + ")";
                        rowsPerWeek = 0;
                        startRow += 2;
                        groupStartRow = startRow;
                    } else {
                        rowsPerWeek += 1;
                        startRow += 1;

                    }
                    row1 = sh.Rows[startRow, Missing.Value];
                    row2 = sh.Rows[startRow + 1, Missing.Value];

                    row = new ProjectRow(row1);
                    nextRow = new ProjectRow(row2);
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SheetGroupByUser: " + ex.Message, true);
                }
            }
        }

        public void SheetGroupByVr(Worksheet sh) {
            int startRow = 3;
            int groupStartRow = 3;
            int summenColumn = 48;
            int rowsPerWeek = 0;
            int anzJobs = 0;
            Range row1 = sh.Rows[startRow, Missing.Value];
            Range row2 = sh.Rows[startRow + 1, Missing.Value];
            UpdateJobSheet(sh, "Vr");
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start SheetGroupByVr", false);
            ProjectRow row = new ProjectRow(row1);
            ProjectRow nextRow = new ProjectRow(row2);

            while (startRow < 1000) {
                try {
                    if (row.IsGesamtSummenZeile) {
                        Range rz = sh.Cells[startRow, "H"];
                        rz.Value = anzJobs;
                        rz.Font.Color = Tools.RGB(0, 191, 191);

                        Range rg = sh.Cells[startRow, "AV"];
                        rg.FormulaR1C1 = "=Sum(R3C" + summenColumn.ToString() + ":R" + (startRow - 1).ToString() + "C" + summenColumn.ToString() + ")/2";
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End SheetGroupByVr", false);
                        return;
                    }

                    if (!row.IsEmpty) {
                        Range cr = sh.Rows[startRow];
                        cr.EntireRow.Hidden = false;

                    }

                    if ((row.Vr != nextRow.Vr)
                        || nextRow.IsGesamtSummenZeile) {
                        rowsPerWeek += 1;
                        Range r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                        r1 = sh.Rows[startRow + 1, Missing.Value];
                        r1.Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeTop].Weight = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 1;
                        r1.Borders[XlBordersIndex.xlEdgeBottom].Weight = 1;
                        r1.Font.Name = Extensions.tpFontBold;
                        r1.Font.Bold = true;
                        r1.Interior.Pattern = XlPattern.xlPatternNone;

                        Range r2 = sh.Cells[startRow + 1, "B"];
                        r2.Value = "Su";
                        r2.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r3 = sh.Cells[startRow + 1, "C"];
                        r3.Value = row.Vr;
                        r3.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                        Range r4 = sh.Cells[startRow + 1, "H"];
                        r4.NumberFormat = "@";
                        r4.HorizontalAlignment = XlHAlign.xlHAlignRight;
                        r4.Value = rowsPerWeek.ToString();
                        r4.Font.Color = Tools.RGB(0, 191, 191);

                        anzJobs += rowsPerWeek;

                        Range r5 = sh.Cells[startRow + 1, "AV"];
                        r5.FormulaR1C1 = "=Sum(R" + groupStartRow.ToString() + "C" + summenColumn.ToString() + ":R" + startRow.ToString() + "C" + summenColumn.ToString() + ")";
                        rowsPerWeek = 0;
                        startRow += 2;
                        groupStartRow = startRow;
                    } else {
                        rowsPerWeek += 1;
                        startRow += 1;

                    }
                    row1 = sh.Rows[startRow, Missing.Value];
                    row2 = sh.Rows[startRow + 1, Missing.Value];

                    row = new ProjectRow(row1);
                    nextRow = new ProjectRow(row2);
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SheetGroupByVr: " + ex.Message, true);
                }
            }
        }

        public void SheetGroupByWeek(Worksheet sh) {
            int startRow = 3;
            int groupStartRow = 3;
            int summenColumn = 48;
            int rowsPerWeek = 0;
            int currentWeek;
            int anzJobs = 0;
            Range row1 = sh.Rows[startRow, Missing.Value];
            Range row2 = sh.Rows[startRow + 1, Missing.Value];
            UpdateJobSheet(sh, "Chronologisch");
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start SheetGroupByWeek (" + sh.Parent.Name + ")", false);
            ProjectRow row = new ProjectRow(row1);
            ProjectRow nextRow = new ProjectRow(row2);

            DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
            Calendar cal = dfi.Calendar;

            currentWeek = cal.GetWeekOfYear(DateTime.Now, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

            while (startRow < 1000) {
                try {
                    RepairHyperlink(sh, startRow, "BB", "C");
                    if (row.IsGesamtSummenZeile) {
                        Range rz = sh.Cells[startRow, "H"];
                        rz.Value = anzJobs;
                        rz.Font.Color = Tools.RGB(0, 191, 191);

                        Range rg = sh.Cells[startRow, "AV"];
                        rg.FormulaR1C1 = "=Sum(R3C" + summenColumn.ToString() + ":R" + (startRow - 1).ToString() + "C" + summenColumn.ToString() + ")/2";
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End SheetGroupByWeek (" + sh.Parent.Name + ")", false);
                        return;
                    }

                    try {
                        if (!row.IsEmpty) {
                            if (row.JobWeek != currentWeek) {
                                Range cr = sh.Rows[startRow];
                                if (!row.JobExported && !row.LeistungExported && !row.IsReadyForExport) {
                                    cr.EntireRow.Hidden = false;
                                } else {
                                    cr.EntireRow.Hidden = true;
                                }
                                if (cr.EntireRow.Hidden && row.JobDatum == new DateTime(1900, 1, 1)) {
                                    if (!row.JobExported) {
                                        cr.EntireRow.Hidden = false;
                                    }
                                }

                            } else {
                                if (row.JobDatum.Year != DateTime.Now.Year) {
                                    Range cr = sh.Rows[startRow];
                                    cr.EntireRow.Hidden = true;
                                }
                            }

                        }
                    } catch (Exception ex) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SheetGroupByWeek: F01 (" + sh.Parent.Name + ")" + ex.Message, true);
                    }

                    try {
                        if ((row.JobWeek < nextRow.JobWeek)
                            || (nextRow.JobDatum.Year > row.JobDatum.Year)
                            || (nextRow.JobDatum.Year == 1900 && !nextRow.IsEmpty)
                            || nextRow.IsGesamtSummenZeile) {
                            rowsPerWeek += 1;
                            Range r1 = sh.Rows[startRow + 1, Missing.Value];
                            r1.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                            r1 = sh.Rows[startRow + 1, Missing.Value];
                            r1.Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                            r1.Borders[XlBordersIndex.xlEdgeTop].Weight = 1;
                            r1.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 1;
                            r1.Borders[XlBordersIndex.xlEdgeBottom].Weight = 1;
                            r1.Font.Name = Extensions.tpFontBold;
                            r1.Font.Bold = true;
                            r1.Interior.Pattern = XlPattern.xlPatternNone;

                            Range r2 = sh.Cells[startRow + 1, "B"];
                            r2.NumberFormat = "@";
                            r2.Value = "Su";
                            r2.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                            Range r3 = sh.Cells[startRow + 1, "C"];
                            r3.NumberFormat = "@";
                            r3.Value = String.Format("{0:yy}-{1:00}", row.JobDatum, row.JobWeek);
                            r3.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                            Range r31 = sh.Cells[startRow + 1, "D"];
                            r31.NumberFormat = "@";
                            r31.Value = String.Format("{0:yy-MM-dd}", row.JobWeekFirstDay)
                                    + " bis " + String.Format("{0:yy-MM-dd}", row.JobWeekLastDay);
                            r31.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;

                            Range r4 = sh.Cells[startRow + 1, "H"];
                            r4.NumberFormat = "@";
                            r4.HorizontalAlignment = XlHAlign.xlHAlignRight;
                            r4.Value = rowsPerWeek.ToString();
                            r4.Font.Color = Tools.RGB(0, 191, 191);

                            anzJobs += rowsPerWeek;

                            Range r5 = sh.Cells[startRow + 1, "AV"];
                            r5.FormulaR1C1 = "=Sum(R" + groupStartRow.ToString() + "C" + summenColumn.ToString() + ":R" + startRow.ToString() + "C" + summenColumn.ToString() + ")";
                            rowsPerWeek = 0;
                            startRow += 2;
                            groupStartRow = startRow;
                        } else {
                            rowsPerWeek += 1;
                            startRow += 1;

                        }
                        row1 = sh.Rows[startRow, Missing.Value];
                        row2 = sh.Rows[startRow + 1, Missing.Value];

                        row = new ProjectRow(row1);
                        nextRow = new ProjectRow(row2);
                    } catch (Exception ex) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SheetGroupByWeek: F02 (" + sh.Parent.Name + ")" + ex.Message, true);
                    }
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SheetGroupByWeek: (" + sh.Parent.Name + ")" + ex.Message, true);
                }
            }
        }

        public void SheetSort(Worksheet sh, int startRow, int endRow, string[] columns, Extensions.tpSortOrder sortOrder) {
            try {
                Range[] ranges = new Range[columns.Length];

                for (int i = 0; i < columns.Length; i++) {
                    ranges[i] = sh.Range[columns[i] + (startRow).ToString() + ":" + columns[i] + (endRow).ToString()];
                }

                sh.Sort.SortFields.Clear();
                foreach (Range r1 in ranges) {
                    string t1 = r1.Address;
                    sh.Sort.SortFields.Add(Key: r1, SortOn: XlSortOn.xlSortOnValues, Order: (sortOrder == Extensions.tpSortOrder.Up ? XlSortOrder.xlAscending : XlSortOrder.xlDescending), DataOption: XlSortDataOption.xlSortNormal);
                }

                Range setRange = sh.Rows[startRow.ToString() + ":" + endRow.ToString()];
                //int test1 = sh.Sort.SortFields.Count;

                ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                ((Microsoft.Office.Interop.Excel.Range)setRange).Select();

                sh.Sort.SetRange(setRange);
                sh.Sort.Header = XlYesNoGuess.xlGuess;
                sh.Sort.MatchCase = false;
                sh.Sort.Orientation = XlSortOrientation.xlSortColumns;
                sh.Sort.SortMethod = XlSortMethod.xlPinYin;
                sh.Sort.Apply();
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SheetSort: (" + sh.Name + "): " + ex.Message, true);
            }
        }

        public void SortInternFile(Worksheet targetSheet, string fileSuffix) {
            ProjectSheet sheet = null;
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "SortInternFile: (" + targetSheet.Parent.Name + "/" + targetSheet.Name + ")", false);
            if (fileSuffix == "je" | fileSuffix == "pl" | fileSuffix == "il") {
                sheet = new ProjectSheet(targetSheet, false);
                SortSheetRows(targetSheet, 3, sheet.ZeileSummeLeistungen, new string[] { "K", "P" });
            }
            if (fileSuffix == "le") {
                sheet = new ProjectSheet(targetSheet, false);

                //Alle Leistungen
                int startRow = sheet.ZeileSummeArbeitszeit + 1;
                int lastRow = sheet.ZeileSummeLeistungen - 1;
                SortSheetRows(targetSheet, startRow, lastRow, new string[] { "K", "P" });

            }
        }

        private void UpdateAe() {
            Workbook ae = null;
            Workbook aesu = null;

            try {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "UpdateAe: Start Update Aepü", false);

                #region Check File exists or is in use

                if (!File.Exists(Settings.Default.DateiAepü)) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "File " + Settings.Default.DateiAepü + " nicht gefunden!", true);
                    return;
                }

                if (Tools.FileIsInUse(Settings.Default.DateiAepü)) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "File " + Settings.Default.DateiAepü + " nicht gefunden!", true);
                    return;
                }

                #endregion

                #region File copy
                string path = Settings.Default.BackupPath + DateTime.Today.AddDays(-1).Year.ToString().Right(2) + @"\bup\" + DateTime.Today.ToString("yyyyMMdd") + @"\";
                if (!Directory.Exists(path)) {
                    Directory.CreateDirectory(path);
                }
                string copyFile = Path.Combine(path, "Aepü.xlsm");
                if (File.Exists(copyFile)) {
                    File.Delete(copyFile);
                }
                File.Copy(Settings.Default.DateiAepüSub, copyFile);

                #endregion

                #region Set Worbbooks & WorkSheets

                ae = exApp.Workbooks.Open(Settings.Default.DateiAepü);
                aesu = exApp.Workbooks.Open(Settings.Default.DateiAepüSub);

                Worksheet aeAe = ae.Sheets["Ae"];
                Worksheet aePü = ae.Sheets["Pü"];
                Worksheet aesuAe = aesu.Sheets["AeSub"];

                #endregion

                bool save = false;
                int startRow = Settings.Default.StartZeile;
                bool startRowSelect = false;
                while (startRowSelect == false & startRow < 10000) {

                    Range sourceRow = aesuAe.Rows[startRow];
                    AeRow aesuRow = new AeRow(sourceRow);
                    if (aesuRow.ProjektName != null) {
                        if (TpTools.Tools.ExistsContentInColumn(aeAe, Settings.Default.SpalteKundenName, aesuRow.ProjektName)) {
                            Range aeRow = TpTools.Office.GetAeRow(aeAe, aesuRow.ProjektName, aesuRow.ProjektBezeichnung, 3);
                            if (aeRow != null) {
                                AeRow targetRow = new AeRow(aeRow);
                                UpdateAeProjektDetails(aesuRow, targetRow);
                                string logText = String.Format("Update Ae: {0}{1}", aesuRow.ProjektName, aesuRow.ProjektBezeichnung);
                                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, logText, false);
                                ClearRow(aesuRow);
                                save = true;
                            }
                        }
                        if (TpTools.Tools.ExistsContentInColumn(aePü, Settings.Default.SpalteKundenName, aesuRow.ProjektName)) {
                            Range aeRow = TpTools.Office.GetAeRow(aePü, aesuRow.ProjektName, aesuRow.ProjektBezeichnung, 3);
                            if (aeRow != null) {
                                AeRow targetRow = new AeRow(aeRow);
                                UpdateAeProjektDetails(aesuRow, targetRow);
                                string logText = String.Format("Update Pü: {0}{1}", aesuRow.ProjektName, aesuRow.ProjektBezeichnung);
                                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, logText, false);
                                ClearRow(aesuRow);
                                save = true;
                            }
                        }
                    } else {
                        startRowSelect = true;
                    }
                    startRow++;
                }
                if (save) {
                    ae.Save();
                }
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End Update Aepü", false);
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateAepü: " + ex.Message, true);
            } finally {
                if (ae != null) {
                    ae.Save();
                    ae.Close(true);
                }
                if (aesu != null) {
                    aesu.Save();
                    aesu.Close(true);
                }
            }
        }

        private void UpdateAeProjektDetails(AeRow source, AeRow target) {

            try {

                #region Projekt

                target.AuftragsDatum = source.AuftragsDatum;

                #endregion

                #region Adresse

                target.Plz = source.Plz;
                target.Ort = source.Ort;
                target.Straße = source.Straße;

                #endregion

                #region Erlös

                target.Erlösminderung = source.Erlösminderung;
                target.Forderungsausfall = source.Forderungsausfall;
                target.RabattGesamt = source.RabattGesamt;
                target.RabattLeistungenGesamt = source.RabattLeistungenGesamt;
                target.RabattProdukteGesamt = source.RabattProdukteGesamt;
                target.Schadenfall = source.Schadenfall;
                target.SummeHandelsware = source.SummeHandelsware;
                target.SummeMontage = source.SummeMontage;
                target.SummeProduktion = source.SummeProduktion;

                #endregion

                #region Kosten

                target.KostenAdministration = source.KostenAdministration;
                target.KostenHandelsware = source.KostenHandelsware;
                target.KostenMontage = source.KostenMontage;
                target.KostenProduktion = source.KostenProduktion;

                #endregion

                #region Zusatzkosten

                target.ZkBaustellenInfrastrukturFix = source.ZkBaustellenInfrastrukturFix;
                target.ZkBaustellenInfrastrukturProzent = source.ZkBaustellenInfrastrukturProzent;
                target.ZkErfüllungsgarantie = source.ZkErfüllungsgarantie;
                target.ZkHaftrücklass = source.ZkHaftrücklass;
                target.ZkProvisionDB = source.ZkProvisionDB;
                target.ZkProvisionErlösFix = source.ZkProvisionErlösFix;
                target.ZkProvisionErlösProzent = source.ZkProvisionErlösProzent;
                target.ZkSkonto = source.ZkSkonto;

                #endregion

                #region Zeitaufwand

                target.ZeitaufwandAdministration = source.ZeitaufwandAdministration;
                target.ZeitaufwandMontage = source.ZeitaufwandMontage;
                target.ZeitaufwandProduktion = source.ZeitaufwandProduktion;

                #endregion

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateAeProjektDetails: " + ex.Message, true);
            }
        }

        private void UpdateJobEvidenz() {
            Workbook jesu = null;
            Workbook targetBook = null;
            try {

                #region Check File exists or is in use

                if (!File.Exists(Jesu)) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "File (" + Jesu + ") nicht gefunden!", true);
                    return;
                }

                if (Tools.FileIsInUse(Jesu)) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "File (" + Jesu + ") ist geöffnet!", true);
                    return;
                }

                #endregion

                #region File copy

                if (Settings.Default.UseBackupJesu) {
                    string path = Settings.Default.BackupPath + DateTime.Today.AddDays(-1).Year.ToString().Right(2) + @"\" + DateTime.Today.ToString("yyyyMMdd") + @"\";
                    if (!Directory.Exists(path)) {
                        Directory.CreateDirectory(path);
                    }
                    string copyFile = Path.Combine(path, "Jesu.xlsx");
                    if (File.Exists(copyFile)) {
                        File.Delete(copyFile);
                    }
                    File.Copy(Jesu, copyFile, true);
                }

                #endregion

                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start Update Jobevidenz", false);
                jesu = exApp.Workbooks.Open(Jesu);

                Worksheet targetSheet;
                foreach (Worksheet sh in jesu.Sheets) {
                    ProjectSheet sheet = new ProjectSheet(sh, true, true);
                    string fileName = Path.Combine(MaPath, sheet.SheetName + "je" + Settings.Default.JeExportFileExtension);

                    if (Tools.FileIsInUse(fileName) | (!File.Exists(fileName))) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "UpdateJobEvidenz: " + String.Format(Resources.FileIsInUseOrNotExists, fileName), false);
                        continue;
                    }

                    targetBook = exApp.Workbooks.Open(fileName);
                    foreach (ProjectRow exRow in sheet.MyRows) {
                        try {
                            targetSheet = targetBook.Sheets[sh.Name];

                            CopyJobevidenzRow(sh, targetSheet, exRow.MyRow);

                        } catch (Exception ex) {
                            LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateJobEvidenz: " + ex.Message, true);
                        }

                    }
                    if (targetBook != null) {
                        targetBook.Save();
                        targetBook.Close(true);
                    }
                    targetBook = null;
                }
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Ende UpdateJobEvidenz", false);
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateJobEvidenz: " + ex.Message, true);
            } finally {
                if (targetBook != null) {
                    targetBook.Save();
                    targetBook.Close(true);
                }
                if (jesu != null) {
                    jesu.Save();
                    jesu.Close(true);
                }
            }
        }

        public void UpdateJobEvidenzIntern(string[] paramFiles, bool runBulk) {
            Workbook wb = null;

            try {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start UpdateJobEvidenzIntern", false);

                string[] myFiles = Directory.GetFiles(MaPath, Settings.Default.FilesInternSearchString);

                if (paramFiles != null) {
                    if (paramFiles.Length != 0) {
                        myFiles = paramFiles;
                    }
                }

                foreach (string file in myFiles) {
                    if (Tools.FileIsInUse(file) | (!File.Exists(file))) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateJobEvidenzIntern: " + String.Format(Resources.FileIsInUseOrNotExists, file), true);
                        continue;
                    }
                    wb = exApp.Workbooks.Open(file);
                    Worksheet sh = wb.Sheets[Tools.Left(wb.Name, 2)];
                    ProjectSheet sheet = new ProjectSheet(sh, true, true);
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "UpdateJobEvidenzIntern: Start " + wb.Name, false);
                    foreach (ProjectRow row in sheet.MyRows) {

                        if (row.IsReadyForInternalJobevidenz) {
                            if (row.Durchführender1 != sh.Name && row.Durchführender2 == "") {
                                row.Veranlasser = sh.Name;
                                row.VeranlasserDatum = DateTime.Now;
                                if (CopyRowToIn(sh, row, row.Durchführender1, row.KundenName, row.Spalte("D").ToString())) {
                                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateJobevidenzIntern: " + row.KundenName + row.Spalte("D") + ", " + row.Durchführender1 + ";" + row.Leistung + ";" + row.JobDatum.ToString(Extensions.tpDateFormat), true);
                                    row.ClearContent();
                                }
                            }
                            row.SetExported("Je");
                        }
                    }
                    wb.Save();
                    if (runBulk) {
                        wb.Close();
                    }
                }

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateJobEvidenzIntern: " + ex.Message, true);
            } finally {
                if (runBulk) {
                    CloseAllWorkbooks(true);
                }
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Ende UpdateJobEvidenzIntern", false);
            }
        }

        public void UpdateJobKataloge(string myPath) {
            try {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start UpdateJobKataloge", false);

                if (myPath == null || myPath.Length < 2) {
                    myPath = MaPath;
                }
                string[] myFiles = Directory.GetFiles(myPath, Settings.Default.FilesNnle);
                string vorlagenfile = Settings.Default.JobVorlagenFile;

                #region Check File exists or is in use


                if (!File.Exists(vorlagenfile)) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "File: " + vorlagenfile + " nicht gefunden!", true);
                    return;
                }

                if (Tools.FileIsInUse(vorlagenfile)) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "File: " + vorlagenfile + " ist geöffnet!", true);
                    return;
                }

                #endregion

                Workbook wbvo = exApp.Workbooks.Open(vorlagenfile);
                Worksheet shvo = wbvo.Worksheets["Vo"];

                foreach (string leFile in myFiles) {

                }
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End UpdateJobKataloge", false);

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateJobKataloge: " + ex.Message, true);
            }
        }

        private void UpdateJobSheet(Worksheet sh, string ansicht) {
            Range r1 = sh.Cells[1, 7];
            r1.Value = ansicht;
            r1.Font.Color = Tools.RGB(0, 191, 191);

        }

        private void UpdateLeistungen(int jahr) {
            Workbook lesu = null;
            Workbook exLe = null;

            try {

                #region Check File exists or is in use

                if (!File.Exists(Lesu)) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateLeistungen: File (" + Lesu + ") not found!", true);
                    return;
                }

                if (Tools.FileIsInUse(Lesu)) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateLeistungen: File " + Lesu + ") is already open!", true);
                    return;
                }

                #endregion

                #region File copy

                if (Settings.Default.UseBackupLesu) {
                    string path = Settings.Default.BackupPath + DateTime.Today.AddDays(-1).Year.ToString().Right(2) + @"\" + DateTime.Today.ToString("yyyyMMdd") + @"\";
                    if (!Directory.Exists(path)) {
                        Directory.CreateDirectory(path);
                    }
                    string copyFile = Path.Combine(path, "Lesu.xlsx");
                    if (File.Exists(copyFile)) {
                        File.Delete(copyFile);
                    }
                    File.Copy(Lesu, copyFile);
                }

                #endregion

                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start UpdateLeistungen", false);
                lesu = exApp.Workbooks.Open(Lesu);

                foreach (Worksheet sh in lesu.Sheets) {
                    ProjectSheet sheet = new ProjectSheet(sh, false, true);
                    foreach (ProjectRow row in sheet.MyRows) {
                        //string fileName = row.Durchführender1 + "le" + DateTime.Today.Year.ToString().Right(2) + Settings.Default.LeExportFileExtension;
                        if (CopyLeistungenInternRow(sh, row, sh.Name, "le", true, "", false, GetLeFreeRowNumber, true)) {
                            row.ClearContent();
                        }
                    }
                }
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Ende UpdateLeistungen", false);
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateLeistungen: " + ex.Message, true);
            } finally {
                if (exLe != null) {
                    exLe.Save();
                    exLe.Close(true);
                }
                if (lesu != null) {
                    lesu.Save();
                    lesu.Close(true);
                }
            }
        }

        private void UpdateLeistungenInternDetail(Worksheet sh, ProjectRow row) {
            if (row.IsReadyForExport & row.JobTime != 0 & ((row.Durchführender1 == sh.Name) || sh.Name == "Bc" || sh.Name == "Sw")) {
                string fileName = row.Durchführender1 + "le" + DateTime.Today.Year.ToString().Right(2) + Settings.Default.LeExportFileExtension;
                bool beOk = CopyLeistungenInternRow(sh, row, row.Durchführender1, "le", true, "", false, GetLeFreeRowNumber);

                if (row.Durchführender2 != "") {
                    fileName = row.Durchführender2 + "le" + DateTime.Today.Year.ToString().Right(2) + Settings.Default.LeExportFileExtension;
                    beOk = CopyLeistungenInternRow(sh, row, row.Durchführender2, "le", true, "", false, GetLeFreeRowNumber);
                }
                if (beOk) {
                    row.ClearContent();
                } else {
                    row.MarkAsFailed();
                }
            }

            if ((row.Durchführender1 != sh.Name) && (sh.Name != "Bc" && sh.Name != "Sw")) {
                row.MarkDurchFuehrender1AsFaild();
            }
        }

        public void UpdateLeistungenIntern(Workbook wb, Worksheet sh) {
            ProjectSheet sheet = new ProjectSheet(sh, true, true);
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "UpdateLeistungenIntern: Start " + wb.Name, false);
            foreach (ProjectRow row in sheet.MyRows) {
                if (!row.IsReadyForExport) {
                    continue;
                }
                //UpdateLeistungenInternDetail(sh, row);
                Range r1 = sh.Rows[row.RowNumber];
                r1.Select();
                r1.Copy();
                CopyRowToLesu(row, row.Durchführender1);
                if (!String.IsNullOrEmpty(row.Durchführender2)) {
                    r1.Select();
                    r1.Copy();
                    CopyRowToLesu(row, row.Durchführender2);
                }
            }
        }

        public void UpdateLeistungenIntern(string[] paramFiles, bool runBulk) {
            Workbook wb = null;
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "UpdateLeistungenIntern(" + runBulk.ToString() + ")", false);
            try {

                string[] myFiles = Directory.GetFiles(MaPath, Settings.Default.FilesInternSearchString);

                if (paramFiles != null) {
                    if (paramFiles.Length != 0) {
                        myFiles = paramFiles;
                    }
                }

                foreach (string file in myFiles) {
                    if (Tools.FileIsInUse(file) | (!File.Exists(file))) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateLeistungenIntern: " + String.Format(Resources.FileIsInUseOrNotExists, file), true);
                        continue;
                    }
                    wb = exApp.Workbooks.Open(file);
                    Worksheet sh = wb.Sheets[wb.Name.Left(2)];
                    ProjectSheet sheet = new ProjectSheet(sh, true, true, true);
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "UpdateLeistungenIntern: Start " + wb.Name, false);
                    foreach (ProjectRow row in sheet.MyRows) {
                        if (!CheckRow(sh, row)) {
                            continue;
                        }
                        UpdateLeistungenInternDetail(sh, row);
                        //toDo: Copy same row in intern project file!!!!

                    }
                    wb.Save();
                    wb.Close(SaveChanges: true);
                }


            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateLeistungenIntern: " + ex.Message, true);
            } finally {
                FinalJobsForInternFiles();
                if (runBulk) {
                    CloseAllWorkbooks(true);
                }
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Ende UpdateLeistungenIntern", false);
            }
        }

        public void UpdateLeistungenFromTecOffice() {
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start UpdateLeistungenFromTecOffice", false);

            CloseAllWorkbooks(true);

            List<JobObj> jobs = CommonDataManager.LoadJobs((short)Extensions.JobStatus.Durchgeführt);
            foreach (JobObj job in jobs) {
                CopyJob2LeFile(job);
                if (job.Status == (short)Extensions.JobStatus.Exportiert) {
                    CommonDataManager.SaveJob(job);
                }
            }
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End UpdateLeistungenFromTecOffice", false);
        }

        #endregion

        #region Functions

        private bool CheckLeistungsRow(Worksheet sh) {
            const string quote = "\"";
            int startRow = 0;
            bool ret = true;
            bool weiter = false;
            int colJobDay = 16;
            int colGesamtZeit = 38;
            int colEinheit = 21;
            //int colDifferenzErfassung = 62;
            int colDifferenzPauschal = 64;
            //int colDifferenzPauschalProzent = 65;
            int colStunden = 63;
            int rowStartSumme = 3;
            int colSumme = 48;
            int maxRowJobTimePerDay = 0;
            int rowCurrentJobDay = 0;

            JobSheet js = new JobSheet(sh);

            if (js.ZeileSummeArbeitszeit == 0 || js.ZeileSummeLeistungen == 0) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CheckLeistungsRow: (" + sh.Parent.Name + "/" + sh.Name + ") - Zeilen = 0", true);
                return false;
            }

            startRow = js.ZeileSummeArbeitszeit + 1;
            try {
                while (!weiter & startRow < 1000) {
                    Range r1 = sh.Rows[startRow, Missing.Value];
                    Range r2 = sh.Rows[startRow - 1, Missing.Value];
                    Range r3 = sh.Rows[startRow + 1, Missing.Value];

                    JobRow jr = new JobRow(r1);
                    JobRow prevJr = new JobRow(r2);
                    JobRow nextJr = new JobRow(r3);

                    if (jr.IsEmpty) {
                        startRow++;
                        continue;
                    }

                    string errMsg = "";
                    if (!jr.DeleteMarkup(out errMsg)) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CheckLeistungsRow: Fehler bei jr.DeleteMarkup():" + errMsg, true);
                    }

                    jr.DeleteBorderUnderline();

                    if (jr.JobTime == 0) {
                        jr.MarkJobTimeAsFaild();
                    }

                    if (jr.JobTime > jr.JobEndTime) {
                        jr.MarkJobEndTimeAsFaild();
                    }

                    if (jr.JobEndTime == 0) {
                        jr.MarkJobEndTimeAsFaild();
                    }

                    if ((prevJr.JobDatum.Date == jr.JobDatum.Date && jr.JobTime > 0) && prevJr.JobEndTime > jr.JobTime) {
                        jr.MarkJobTimeAsFaild();
                        prevJr.MarkJobEndTimeAsFaild();
                    }

                    if (jr.JobEndTime > 0) {
                        maxRowJobTimePerDay = startRow;
                    }

                    rowCurrentJobDay = js.GetRowForWorkDay(jr.JobDatum);

                    //1. Spalte: Differenz Erfassung
                    if (prevJr.IsEmpty || (jr.JobDatum.Date != prevJr.JobDatum.Date)) {
                        rowStartSumme = startRow;
                        jr.AWR1C1DifferenzErfassung = "=(R" + startRow.ToString() + "C" + colJobDay.ToString() + "-R" + (rowCurrentJobDay > 0 ? rowCurrentJobDay : startRow).ToString() + "C" + colJobDay.ToString() + ")*24";
                        jr.DeleteBorderUnderline();

                    } else {
                        jr.AWR1C1DifferenzErfassung = "=IF(R" + startRow.ToString() + "C" + colJobDay.ToString() + ">0,(R" + startRow.ToString() + "C" + colJobDay.ToString() + "-R" + (startRow - 1).ToString() + "C" + (colJobDay + 1).ToString() + ")*24,0)";
                        jr.DeleteBorderUnderline();

                    }

                    if (nextJr.IsEmpty || (jr.JobDatum.Date != nextJr.JobDatum.Date)) {

                        jr.AWR1C1DifferenzErfassung = "=(R" + (rowCurrentJobDay > 0 ? rowCurrentJobDay : startRow).ToString() + "C" + (colJobDay + 1).ToString() + "-R" + maxRowJobTimePerDay.ToString() + "C" + (colJobDay + 1).ToString() + ")*24";
                        jr.AWR1C1SumPerDay = "=Sum(R" + rowStartSumme.ToString() + "C" + colSumme.ToString() + ":R" + startRow.ToString() + "C" + colSumme.ToString() + ")";
                        jr.DrawBorderUnderline();

                        if (rowCurrentJobDay > 0) {
                            Range c1 = sh.Cells[rowCurrentJobDay, "C"];
                            c1.FormulaR1C1 = "=R" + startRow.ToString() + "C" + (colSumme + 1).ToString();
                        }
                    }

                    //2. Spalte Stunden
                    jr.AWR1C1Stunden = "=(R" + startRow.ToString() + "C" + (colJobDay + 1).ToString() + "-R" + startRow.ToString() + "C" + colJobDay.ToString() + ")*24";

                    //3. Spalte Differenz Pauschalzeit
                    jr.AWR1C1DifferenzPauschal = "=IF(R" + startRow.ToString() + "C" + colGesamtZeit.ToString() + "=" + quote + "PA" + quote + ",R" + startRow.ToString() + "C" + colStunden.ToString() + "-R" + startRow.ToString() + "C" + colEinheit.ToString() + ",0)";

                    //4. Spalte Differnenz Pauschal Prozent
                    jr.AWR1C1DifferenzPauschalProzent = "=IF(R" + startRow.ToString() + "C" + colDifferenzPauschal.ToString() + "<>0,R" + startRow.ToString() + "C" + colDifferenzPauschal.ToString() + "/R" + startRow.ToString() + "C" + colEinheit.ToString() + "*100, 0)";

                    jr.IsProcessed = true;
                    startRow++;


                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CheckLeistungsRow: " + ex.Message, true);
                return false;
            }
            return ret;
        }

        private bool CheckRow(Worksheet sh, ProjectRow row) {
            string cellValue = ""; //Tools.Nz(Convert.ToString(row.Spalte("C")), "");
            if (row.MyRow.Cells[1, "C"].Value != null) {
                cellValue = row.MyRow.Cells[1, "C"].Value.ToString();
            }

            if (row.Durchführender1 != "" && !Office.CheckUserFileExists(row.Durchführender1, MaPath)) {
                Range r1 = sh.Cells[row.RowNumber, "N"];
                r1.Interior.Color = Tools.RGB(0, 255, 255);
                return false;
            }

            if (row.Durchführender2 != "" && !Office.CheckUserFileExists(row.Durchführender2, MaPath)) {
                Range r1 = sh.Cells[row.RowNumber, "O"];
                r1.Interior.Color = Tools.RGB(0, 255, 255);
                return false;
            }

            if (cellValue == "") {
                Range r1 = sh.Cells[row.RowNumber, "C"];
                r1.Interior.Color = Tools.RGB(0, 255, 255);
                return false;

            }
            if (row.Durchführender1 == "") {
                Range r1 = sh.Cells[row.RowNumber, "N"];
                r1.Interior.Color = Tools.RGB(0, 255, 255);
                return false;
            }
            if (row.Fahrzeug == "") {
                Range r1 = sh.Cells[row.RowNumber, "M"];
                r1.Interior.Color = Tools.RGB(0, 255, 255);
                return false;
            }
            if (row.JobDatum == new DateTime(1900, 1, 1)) {
                Range r1 = sh.Cells[row.RowNumber, "K"];
                r1.Interior.Color = Tools.RGB(0, 255, 255);
                return false;
            }
            cellValue = Tools.Nz(Convert.ToString(row.Spalte("U")), "");
            if (cellValue == "") {
                Range r1 = sh.Cells[row.RowNumber, "U"];
                r1.Interior.Color = Tools.RGB(0, 255, 255);
                return false;
            }

            //if(DateTime.Today.Day > 5) {
            //if((row.JobDatum.Year == DateTime.Today.Year & row.JobDatum.Month < DateTime.Today.Month) | (row.JobDatum.Year < DateTime.Today.Year)) {
            //if(row.JobDatum < new DateTime(2011, 12, 1)) {
            //    Range r1 = sh.Cells[row.RowNumber, "K"];
            //    r1.Interior.Color = Tools.RGB(0, 255, 255);
            //    return false;
            //}
            //}

            //}

            if (row.JobEndTime < row.JobTime) {
                Range r1 = sh.Cells[row.RowNumber, "Q"];
                r1.Interior.Color = Tools.RGB(0, 255, 255);
                return false;
            }

            if (row.IsProjectJob) {
                return false;
            }
            return true;
        }

        public Range GetNewRow(Worksheet sh, int startRow) {
            Range row = sh.Rows[startRow];
            return row;
        }

        public int GetInFreeRowNumber(Worksheet sh, string bereich, string teilBereich) {
            int retRow = 0;
            int lastFreeRow = 5;
            startRow = 5;
            int firstAreaRow = 5;
            bool selectRows = false;
            while (!selectRows & startRow < 5000) {
                Range row = sh.Rows[startRow];
                ProjectRow targetRow = new ProjectRow(row);
                if (targetRow.KundenName != "") {

                    if (targetRow.KundenName == bereich & targetRow.Spalte("D").ToString().ToUpper() == teilBereich.ToUpper()) {
                        if (targetRow.JobDatum != new DateTime(1900, 1, 1)) {
                            firstAreaRow = startRow + 1;
                        }
                        if (targetRow.JobDatum == new DateTime(1900, 1, 1)) {
                            if ((startRow - lastFreeRow) < 2) {
                                Range r1 = sh.Rows[startRow - 1];
                                ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                                r1.Select();
                                r1.Copy();
                                r1.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                                firstAreaRow = startRow;
                                lastFreeRow = startRow;
                            }
                            return lastFreeRow;
                        }
                    } else {
                        if (targetRow.Spalte("B").ToString().ToUpper() == "SU") {
                            firstAreaRow = startRow + 1;
                        }
                    }
                } else {
                    if ((startRow - 1) != lastFreeRow) {
                        lastFreeRow = startRow;
                    }
                }
                startRow++;
            }
            return retRow;
        }

        public int GetJeFreeRowNumber(string bereich, Worksheet sh) {
            startRow = 3;
            int freeRow = 0;
            try {
                bool selectRows = false;
                while (selectRows == false & startRow < 5000) {
                    Range r1 = sh.Cells[startRow, 2];
                    Range r2 = sh.Cells[startRow, 3];
                    Range r3 = sh.Cells[startRow, "N"];
                    string t1 = Tools.Nz(Convert.ToString(r1.Value), "");
                    string t2 = t1 + Tools.Nz(Convert.ToString(r2.Value), "");

                    if (t2 != "" | r3.Value != null) {

                        if (t2.ToUpper().Contains("LEISTUNGEN")) {
                            freeRow = startRow;
                            selectRows = true;
                            ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                            Range row = sh.Rows[startRow - 1];
                            row.Select();
                            row.Copy();
                            row.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                            row = sh.Rows[startRow, Missing.Value];
                            ProjectRow row1 = new ProjectRow(row);
                            row.ClearContents();
                            row.Select();
                        }

                    } else {
                        freeRow = startRow;
                        selectRows = true;
                    }
                    startRow++;
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "GetJeFreeRowNumber: " + ex.Message, true);
            }
            return freeRow;
        }

        private int GetLeFreeRowNumber(string bereich, Worksheet sh) {
            startRow = 3;
            int freeRow = 0;
            int lastRow = 0;
            ProjectSheet sheet = new ProjectSheet(sh, true);
            try {

                bool selectRows = false;
                switch (bereich) {
                    case "Leistungen":
                    case "le":
                        startRow = (sheet.ZeileSummeArbeitszeit == 0 ? 3 : sheet.ZeileSummeArbeitszeit + 1);
                        lastRow = sheet.ZeileSummeLeistungen;
                        break;
                    default:
                        startRow = 3;
                        lastRow = 5;
                        break;
                }

                selectRows = false;
                while (selectRows == false & startRow < 1000) {
                    Range r1 = sh.Cells[startRow, 2];
                    Range r2 = sh.Cells[startRow, 3];
                    Range r3 = sh.Cells[startRow, "N"];
                    string t1 = Tools.Nz(Convert.ToString(r1.Value), "");
                    string t2 = t1 + Tools.Nz(Convert.ToString(r2.Value), "");
                    if ((t2 != "" & r3.Value != null) | (startRow == lastRow)) {
                        if (startRow == lastRow) {
                            freeRow = startRow;
                            selectRows = true;
                            ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                            Range row = sh.Rows[startRow - 1, Missing.Value];
                            row.Select();
                            row.Copy();
                            row.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                        }
                    } else {
                        freeRow = startRow;
                        selectRows = true;
                    }
                    startRow++;
                }

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "GetLeFreeRowNumber: " + ex.Message, true);
            }
            return freeRow;
        }

        private string GetRowDescription(Range row) {
            Range r1 = row.Cells[1, 2];
            Range r2 = row.Cells[1, 3];
            Range r3 = row.Cells[1, 4];
            string t1 = ((string)Tools.Nz(Convert.ToString(r1.Value), "") + " " + Tools.Nz(Convert.ToString(r2.Value), "") + " " + Tools.Nz(Convert.ToString(r3.Value), "")).Trim();
            return t1;
        }

        private static int GetFirstFreeRowNumber(int copyRow, Worksheet sh) {
            Range row = sh.Rows[copyRow];
            Range r1 = row.Cells[1, 3];
            Range r2 = row.Cells[1, "N"];
            while (r1.Value != null && r2.Value != null) {
                copyRow++;
                row = sh.Rows[copyRow];
                r1 = row.Cells[1, 3];
                r2 = row.Cells[1, "N"];
            }
            return copyRow;
        }

        #endregion

        #region Destructor

        ~Agent() {
            if (!ExcelIsStarted()) {
                exApp.Quit();
            }
        }

        #endregion

    }
}
